Power Query: Fetching a column values from another column

[Nooby]

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:

[amitchandak]

@ImkeF , can you help

[ImkeF · Accepted solution]

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]

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!