SOLVED

how to replacer by lists

%3CLINGO-SUB%20id%3D%22lingo-sub-2120716%22%20slang%3D%22en-US%22%3Ehow%20to%20replacer%20by%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120716%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20correct%20usual%20mistakes%20in%20an%20Excel%20sheet%20that%20i%20regularly%20receive%2C%20using%20this%20table%20replacements%20but%20i%20am%20no%20able%20to%20connect%20TblReplacement01%2C%2002%2C%20etc%20to%20the%20main%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anybody%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%20Juan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2120716%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121458%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20replacer%20by%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121458%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F145009%22%20target%3D%22_blank%22%3E%40juan%20jimenez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETwo%20ways%3C%2FP%3E%0A%3CP%3E1)%20Set%20%22Ignore%20the%20privacy%20level%22%20in%20Data-%26gt%3BGet%20Data-%26gt%3BQuery%20Options.%20Easiest%20way%2C%20but%20I'd%20don't%20recommend%20it.%20Within%20one%20file%20security%20is%20not%20an%20issue%2C%20but%20that's%20will%20be%20possible%20incompatibility.%20Now%20with%20Power%20Query%20on%20Power%20BI%2C%20in%20future%20maybe%20with%20next%20versions%20of%20Power%20Query%20for%20Excel.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2)%20Much%20better%20is%20to%20rebuild%20the%20scheme.%20One%20data%20source%20-%20one%20and%20separate%20query.%20In%20each%20of%20it%20make%20only%20initial%20transformations%20like%20filtering%2C%20removing%20extra%20columns%2C%20etc.%20Other%20words%20only%20get%20data.%20Do%20all%20transformations%20with%20data%20(merging%2C%20etc)%20in%20separate%20queries.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20your%20case%3A%3C%2FP%3E%0A%3CP%3E-%20you%20already%20have%20tblReplaceNN%20queries%20for%20each%20of%20tables%20with%20replacement%20pairs.%3C%2FP%3E%0A%3CP%3E-%20now%20let%20create%20which%20only%20get%20the%20main%20table%20%3CSTRONG%3Ehorario%2000%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Origen%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Tabla8%22%5D%7D%5BContent%5D%0Ain%0A%20%20%20%20Origen%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EAdd%20filtering%2C%20type%20changing%2C%20etc%20if%20necessary%3C%2FP%3E%0A%3CP%3E-%20Final%20transformation%20we%20do%20in%20separate%20query%20horario%20combining%20all%20these%20tables%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20%23%22horario%2000%22%2C%0A%0A%20%20%20%20List1%20%3D%20List.Zip(%7BtblReplace01%5BBefore%5D%2CtblReplace01%5BAfter%5D%7D)%2C%0A%20%20%20%20Replace1%20%3D%20Table.TransformColumns(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22grupo%22%2Ceach%20List.Accumulate(List1%2C_%2C(x%2Cy)%3D%26gt%3BText.Replace(x%2Cy%7B0%7D%2Cy%7B1%7D))%2Ctype%20text%7D%7D%0A%20%20%20%20)%2C%0A%20%20%20%20List2%20%3D%20List.Zip(%7BtblReplace02%5BBefore%5D%2CtblReplace02%5BAfter%5D%7D)%2C%0A%20%20%20%20Replace2%20%3D%20Table.TransformColumns(%0A%20%20%20%20%20%20%20%20Replace1%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22cliente%22%2Ceach%20List.Accumulate(List2%2C_%2C(x%2Cy)%3D%26gt%3BText.Replace(x%2Cy%7B0%7D%2Cy%7B1%7D))%2Ctype%20text%7D%7D%0A%20%20%20%20)%2C%0A%20%20%20%20List3%20%3D%20List.Zip(%7BtblReplace03%5BBefore%5D%2CtblReplace03%5BAfter%5D%7D)%2C%0A%20%20%20%20Replace3%20%3D%20Table.TransformColumns(%0A%20%20%20%20%20%20%20%20Replace2%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22tarea1%22%2Ceach%20List.Accumulate(List3%2C_%2C(x%2Cy)%3D%26gt%3BText.Replace(x%2Cy%7B0%7D%2Cy%7B1%7D))%2Ctype%20text%7D%7D%0A%20%20%20%20)%2C%0A%20%20%20%20List4%20%3D%20List.Zip(%7BtblReplace04%5BBefore%5D%2CtblReplace04%5BAfter%5D%7D)%2C%0A%20%20%20%20Replace4%20%3D%20Table.TransformColumns(%0A%20%20%20%20%20%20%20%20Replace3%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22tarea2%22%2Ceach%20List.Accumulate(List4%2C_%2C(x%2Cy)%3D%26gt%3BText.Replace(x%2Cy%7B0%7D%2Cy%7B1%7D))%2Ctype%20text%7D%7D%0A%20%20%20%20)%2C%0A%20%20%20%20List5%20%3D%20List.Zip(%7BtblReplace05%5BBefore%5D%2CtblReplace05%5BAfter%5D%7D)%2C%0A%20%20%20%20Replace5%20%3D%20Table.TransformColumns(%0A%20%20%20%20%20%20%20%20Replace4%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22tarea3%22%2Ceach%20List.Accumulate(List5%2C_%2C(x%2Cy)%3D%26gt%3BText.Replace(x%2Cy%7B0%7D%2Cy%7B1%7D))%2Ctype%20text%7D%7D%0A%20%20%20%20)%0Ain%0A%20%20%20%20Replace5%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121399%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20replacer%20by%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121399%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20for%20your%20quick%20answer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhen%20i%20try%20to%20run%20the%20query%2C%20it%20stops%20saying%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22Formula.Firewall%3A%20Consulta%20'horario'%20(paso%20'Replace1')%20references%20other%20queries%20or%20steps%2C%20so%20it%20may%20not%20directly%20access%20a%20data%20source.%20Please%20rebuild%20this%20data%20combination.%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120945%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20replacer%20by%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120945%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F145009%22%20target%3D%22_blank%22%3E%40juan%20jimenez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20forgot%20the%20comma%20before%20List1%20step%20in%20main%20query.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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

4 Replies

@juan jimenez 

You forgot the comma before List1 step in main query.

@Sergei Baklan 

 

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."

best response confirmed by juan jimenez (Contributor)
Solution

@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
Thank you very much for your answer. I didn't understand any of the program lines your excel worked perfectly.
I wonder if there is any online course where I could learn something more complicated than the usual commands that use from then screen automatic bottoms