cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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.FETCH1TABLE2.FETCH2TABLE2.FETCH3
3XXYYZZTT123
tXXYYZZTT442
rXXYYZZTT987
nullnullnull
nullnullnull
1PPRRQQSSr2d

 

I'm not able to do it with standard buttons:

NOT STANDARD.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Power Query: Fetching a column values from another column

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

 

 

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Power Query: Fetching a column values from another column

@ImkeF , can you help

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Super User III
Super User III

Re: Power Query: Fetching a column values from another column

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

 

 

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Highlighted
Helper I
Helper I

Re: Power Query: Fetching a column values from another column

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!

 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors