Forum Discussion

Nooby's avatar
Nooby
Helper I
6 years ago
Solved

Power Query: Fetching a column values from another column

Hello everyone,   I can do this on sql very easy but I have to do it on power bi power query editor with M. (not DAX)   There is a column with the values:   TABLE1.CODE_COLUMN XXYYZZTT ...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi

    I've split up the process into (hopefully) digestible steps (please also check attached file):

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYo7DoAgEETvQm3DLol6C34FQihIFnqxwOOLKJPXzMwLgXHAjnPH4b21X+uwuAQ2VyEA8H5TqOVxUaKSW811Ovu2jiNdVBBEyvX8xYIogHd/dC6l1koZU4GAxfgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    ListOfFoundCodes = Table.AddColumn(#"Changed Type", "PositionOfText", each List.Transform(Table1_Code_Column[Column1], (x) => Text.PositionOf([Column1], x))),
    IndexOfCode = Table.AddColumn(ListOfFoundCodes, "IndexFoundCode", each List.PositionOfAny([PositionOfText], {0..1000})),
    PositionWithinDirtyData = Table.AddColumn(IndexOfCode, "PositionOfTextWhereFound", each List.Max([PositionOfText])),
    Fetch1 = Table.AddColumn(PositionWithinDirtyData, "Fetch1", each try Text.Range([Column1],[PositionOfTextWhereFound]-1, 1) otherwise null),
    Fetch2 = Table.AddColumn(Fetch1, "Fetch2", each if [IndexFoundCode] = -1 then null  else Table1_Code_Column{[IndexFoundCode]}[Column1]),
    Fetch3 = Table.AddColumn(Fetch2, "Fetch3", each if [IndexFoundCode] = -1 then null else Text.Range([Column1],[PositionOfTextWhereFound] + Text.Length([Fetch2]),3))
in
    Fetch3

     

     

     

     

ImkeF's avatar
ImkeF
Community Champion
6 years ago

Hi

I've split up the process into (hopefully) digestible steps (please also check attached file):

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYo7DoAgEETvQm3DLol6C34FQihIFnqxwOOLKJPXzMwLgXHAjnPH4b21X+uwuAQ2VyEA8H5TqOVxUaKSW811Ovu2jiNdVBBEyvX8xYIogHd/dC6l1koZU4GAxfgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    ListOfFoundCodes = Table.AddColumn(#"Changed Type", "PositionOfText", each List.Transform(Table1_Code_Column[Column1], (x) => Text.PositionOf([Column1], x))),
    IndexOfCode = Table.AddColumn(ListOfFoundCodes, "IndexFoundCode", each List.PositionOfAny([PositionOfText], {0..1000})),
    PositionWithinDirtyData = Table.AddColumn(IndexOfCode, "PositionOfTextWhereFound", each List.Max([PositionOfText])),
    Fetch1 = Table.AddColumn(PositionWithinDirtyData, "Fetch1", each try Text.Range([Column1],[PositionOfTextWhereFound]-1, 1) otherwise null),
    Fetch2 = Table.AddColumn(Fetch1, "Fetch2", each if [IndexFoundCode] = -1 then null  else Table1_Code_Column{[IndexFoundCode]}[Column1]),
    Fetch3 = Table.AddColumn(Fetch2, "Fetch3", each if [IndexFoundCode] = -1 then null else Text.Range([Column1],[PositionOfTextWhereFound] + Text.Length([Fetch2]),3))
in
    Fetch3

 

 

 

 

Nooby's avatar
Nooby
Helper I
6 years ago

Dear ImkeF ,

 

it works like a charm, thank you very much.

 

I see my sql knowledge doesn't help at all, it seems so complicated that I couldn't solve it on my own.

 

I do everything on sql and trying to get familiar with Power BI lately. What source would you offer to be able to write this code of yours, 'cause the ones I studied didn't help at all.

 

and thank you amitchandak!