Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Extract Text from referenced column

Hi everybody,   hope you are doing good.   In my Power BI report I want to filter my bank transfers according certain categories, e. g. food, medicine, petrol, gym, ...   I got two tables: 1. ...
  • ziying35's avatar
    ziying35
    5 years ago

    Anonymous 

    // Table1
let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("jc0xDoIwGIbhq3zpqmkicXIr2IBRKBEd1DhU+dGGUEhpJ+PdXTxAD/C87+3DUm17eKft3JGDsZ5sSy2m4KZxJrZhohRXVaEWl1JWpwbyfFS1RMMzLji2kn2XMZXDdoe8TAsskCmOfR7nKLixMz5ZIx8eRZyRLfUajQ9dh1WSxKFaZQrSWGeebx/sax40ud7T/3v/AQ==",BinaryEncoding.Base64),Compression.Deflate))),
    fx = (str)=> Table2[Category]{List.PositionOf(Table2[Store], str, 0, (x,y)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase))}?,
    dic = Table.Buffer(Table2),
    result = Table.AddColumn(Source, "Category", each fx([Bank transfer intended purpose]))
in
    result

// Table2
let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WCi7JL0pVslJyzE2sys9T0lFyTixJTc8vqgSKuZXmKdXqIKnJSclEU5Gfn6IAkUZW6JqSmp1IlMqA/OR8dEuL8jJLStFNLC3KT8ssMTJBVexematUGwsA",BinaryEncoding.Base64),Compression.Deflate)))
in
    Source
ziying35's avatar
ziying35
Impactful Individual
5 years ago

Anonymous 

// Table1
let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("jc0xDoIwGIbhq3zpqmkicXIr2IBRKBEd1DhU+dGGUEhpJ+PdXTxAD/C87+3DUm17eKft3JGDsZ5sSy2m4KZxJrZhohRXVaEWl1JWpwbyfFS1RMMzLji2kn2XMZXDdoe8TAsskCmOfR7nKLixMz5ZIx8eRZyRLfUajQ9dh1WSxKFaZQrSWGeebx/sax40ud7T/3v/AQ==",BinaryEncoding.Base64),Compression.Deflate))),
    fx = (str)=> Table2[Category]{List.PositionOf(Table2[Store], str, 0, (x,y)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase))}?,
    dic = Table.Buffer(Table2),
    result = Table.AddColumn(Source, "Category", each fx([Bank transfer intended purpose]))
in
    result

// Table2
let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WCi7JL0pVslJyzE2sys9T0lFyTixJTc8vqgSKuZXmKdXqIKnJSclEU5Gfn6IAkUZW6JqSmp1IlMqA/OR8dEuL8jJLStFNLC3KT8ssMTJBVexematUGwsA",BinaryEncoding.Base64),Compression.Deflate)))
in
    Source
Anonymous's avatar
Anonymous
Not applicable
5 years ago

ziying35, Anonymous , lbendlin thank you for your answers. I just tried the code but it is not working properly.

 

I´am very new to the topic so I have some open points. For my current understanding: 

 

 

Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WCi7JL0pVslJyzE2sys9T0lFyTixJTc8vqgSKuZXmKdXqIKnJSclEU5Gfn6IAkUZW6JqSmp1IlMqA/OR8dEuL8jJLStFNLC3KT8ssMTJBVexematUGwsA",BinaryEncoding.Base64),Compression.Deflate)))

 

 

This is the code for my data source. In this case from a JSON document. If I am getting my data from an excel file it would look like this: (I´am asking because all my data is from excel)

Source = Excel.Workbook(File.Contents("C:\Users\ABC\Desktop\PowerBI_Projekt\Finanzen\Buchungen\Ordnerpfade.xlsx"), null, true),

 

I also did some changes at the columns like:

#"Changed Type" = Table.TransformColumnTypes(tab_QuelleSparkasse_Table,{{"Sparkasse", type text}}),

(Table1 = tab_QuelleSparkasse)

 

 

As also the categories are from excel the code for Table2 looks like: (Table2 = tab_KatRef)Source = Excel.Workbook(File.Contents("C:\Users\ABS\Desktop\PowerBI_Projekt\Finanzen\Buchungen\Kategorien.xlsx"), null, true),
tab_KatRef_Table = Source{[Item="tab_KatRef",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(tab_KatRef_Table,{{"Kategorie", type text}, {"Namen", type text}})
in
#"Changed Type"

 

 

For my understanding I "only" have to add

fx = (str)=> Table2[Category]{List.PositionOf(Table2[Store], str, 0, (x,y)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase))}?,
    dic = Table.Buffer(Table2),
    result = Table.AddColumn(Source, "Category", each fx([Bank transfer intended purpose]))
in
    result

to table 1?

 

As follows:

let
Source = Excel.Workbook(File.Contents("C:\Users\pme2abt\Desktop\PowerBI_Projekt\Finanzen\Buchungen\Ordnerpfade.xlsx"), null, true),
tab_QuelleSparkasse_Table = Source{[Item="tab_QuelleSparkasse",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(tab_QuelleSparkasse_Table,{{"Sparkasse", type text}}),

fx = (str)=> tab_KatRef[Kategorie]{List.PositionOf(tab_KatRef[Namen], str, 0, (x,y)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase))}?,
dic = Table.Buffer(tab_KatRef),
result = Table.AddColumn(Source, "Kategorie", each fx([Bank transfer intended purpose]))

 

in

 result

  • Bohumil_Uhrin's avatar
    Bohumil_Uhrin
    Helper II
    5 years ago

    Anonymous, 

    Source in result row replace with #"Changed Type"

    tab_KatRef in fx row replace with dic - this change is not necessary, but it will speed up the query (otherwise the buffer is not used)

    So your code should look like this for Table1, changes are bolded:

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\pme2abt\Desktop\PowerBI_Projekt\Finanzen\Buchungen\Ordnerpfade.xlsx"), null, true),
    tab_QuelleSparkasse_Table = Source{[Item="tab_QuelleSparkasse",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(tab_QuelleSparkasse_Table,{{"Sparkasse", type text}}),

    fx = (str)=> dic[Kategorie]{List.PositionOf(dic[Namen], str, 0, (x,y)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase))}?,
    dic = Table.Buffer(tab_KatRef),
    result = Table.AddColumn(#"Changed Type", "Kategorie", each fx([Bank transfer intended purpose]))

    in

     result