how to replacer by lists

juan jimenez 

Two 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