Forum Discussion
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 |
| PPRRQQSS |
And there is another column in another table
TABLE2.DIRTY_DATA
| 123123XXYYZZTT123123123 |
| XXYYZZTT44223xxxxxfdwe |
| dadfewrerXXYYZZTT987 |
| asdf324aerq |
| daf33421adf |
| 1PPRRQQSSr2d2 |
I have to search the first column values one by one and when I find the value, I have to extract the first value before it, itself and following 3 characters with it.
The new 3 columns would be:
| TABLE2.FETCH1 | TABLE2.FETCH2 | TABLE2.FETCH3 |
| 3 | XXYYZZTT | 123 |
| t | XXYYZZTT | 442 |
| r | XXYYZZTT | 987 |
| null | null | null |
| null | null | null |
| 1 | PPRRQQSS | r2d |
I'm not able to do it with standard buttons:
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
3 Replies
ImkeF , can you help
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 Fetch3Dear 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!
