Feb 10 2021 10:06 AM
hi,
I am trying to correct usual mistakes in an Excel sheet that i regularly receive, using this table replacements but i am no able to connect TblReplacement01, 02, etc to the main table.
Can anybody help?
Thanks in advance, Juan
Feb 10 2021 12:32 PM
You forgot the comma before List1 step in main query.
Feb 11 2021 12:17 AM
thank you for your quick answer.
when i try to run the query, it stops saying
"Formula.Firewall: Consulta 'horario' (paso 'Replace1') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."
Feb 11 2021 01:29 AM
SolutionTwo ways
1) Set "Ignore the privacy level" in Data->Get Data->Query Options. Easiest way, but I'd don't recommend it. Within one file security is not an issue, but that's will be possible incompatibility. Now with Power Query on Power BI, in future maybe with next versions of Power Query for Excel.
2) Much better is to rebuild the scheme. One data source - one and separate query. In each of it make only initial transformations like filtering, removing extra columns, etc. Other words only get data. Do all transformations with data (merging, etc) in separate queries.
In your case:
- you already have tblReplaceNN queries for each of tables with replacement pairs.
- now let create which only get the main table horario 00
let
Origen = Excel.CurrentWorkbook(){[Name="Tabla8"]}[Content]
in
Origen
Add filtering, type changing, etc if necessary
- Final transformation we do in separate query horario combining all these tables
let
Source = #"horario 00",
List1 = List.Zip({tblReplace01[Before],tblReplace01[After]}),
Replace1 = Table.TransformColumns(
Source,
{{"grupo",each List.Accumulate(List1,_,(x,y)=>Text.Replace(x,y{0},y{1})),type text}}
),
List2 = List.Zip({tblReplace02[Before],tblReplace02[After]}),
Replace2 = Table.TransformColumns(
Replace1,
{{"cliente",each List.Accumulate(List2,_,(x,y)=>Text.Replace(x,y{0},y{1})),type text}}
),
List3 = List.Zip({tblReplace03[Before],tblReplace03[After]}),
Replace3 = Table.TransformColumns(
Replace2,
{{"tarea1",each List.Accumulate(List3,_,(x,y)=>Text.Replace(x,y{0},y{1})),type text}}
),
List4 = List.Zip({tblReplace04[Before],tblReplace04[After]}),
Replace4 = Table.TransformColumns(
Replace3,
{{"tarea2",each List.Accumulate(List4,_,(x,y)=>Text.Replace(x,y{0},y{1})),type text}}
),
List5 = List.Zip({tblReplace05[Before],tblReplace05[After]}),
Replace5 = Table.TransformColumns(
Replace4,
{{"tarea3",each List.Accumulate(List5,_,(x,y)=>Text.Replace(x,y{0},y{1})),type text}}
)
in
Replace5
Feb 12 2021 06:03 AM
Feb 11 2021 01:29 AM
SolutionTwo ways
1) Set "Ignore the privacy level" in Data->Get Data->Query Options. Easiest way, but I'd don't recommend it. Within one file security is not an issue, but that's will be possible incompatibility. Now with Power Query on Power BI, in future maybe with next versions of Power Query for Excel.
2) Much better is to rebuild the scheme. One data source - one and separate query. In each of it make only initial transformations like filtering, removing extra columns, etc. Other words only get data. Do all transformations with data (merging, etc) in separate queries.
In your case:
- you already have tblReplaceNN queries for each of tables with replacement pairs.
- now let create which only get the main table horario 00
let
Origen = Excel.CurrentWorkbook(){[Name="Tabla8"]}[Content]
in
Origen
Add filtering, type changing, etc if necessary
- Final transformation we do in separate query horario combining all these tables
let
Source = #"horario 00",
List1 = List.Zip({tblReplace01[Before],tblReplace01[After]}),
Replace1 = Table.TransformColumns(
Source,
{{"grupo",each List.Accumulate(List1,_,(x,y)=>Text.Replace(x,y{0},y{1})),type text}}
),
List2 = List.Zip({tblReplace02[Before],tblReplace02[After]}),
Replace2 = Table.TransformColumns(
Replace1,
{{"cliente",each List.Accumulate(List2,_,(x,y)=>Text.Replace(x,y{0},y{1})),type text}}
),
List3 = List.Zip({tblReplace03[Before],tblReplace03[After]}),
Replace3 = Table.TransformColumns(
Replace2,
{{"tarea1",each List.Accumulate(List3,_,(x,y)=>Text.Replace(x,y{0},y{1})),type text}}
),
List4 = List.Zip({tblReplace04[Before],tblReplace04[After]}),
Replace4 = Table.TransformColumns(
Replace3,
{{"tarea2",each List.Accumulate(List4,_,(x,y)=>Text.Replace(x,y{0},y{1})),type text}}
),
List5 = List.Zip({tblReplace05[Before],tblReplace05[After]}),
Replace5 = Table.TransformColumns(
Replace4,
{{"tarea3",each List.Accumulate(List5,_,(x,y)=>Text.Replace(x,y{0},y{1})),type text}}
)
in
Replace5