Fetching a column values from another column

[Nooby]

Hello everyone,

I have a column which has the values such as:

CODE COLUMN

XXYYZZTT

PPRRQQSS

..

..

..

And there is another column in another table

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:

fetched column1	fetched column2	fetched column3
3	XXYYZZTT	123
t	XXYYZZTT	442
r	XXYYZZTT	987
null	null	null
null	null	null
1	PPRRQQSS	r2d
..	..	..
..	..	..

This is very easy in SQL but I'm not able to do it on Power BI.

Here's a screenshot to make it a bit more clear:

Thank you in advance!

[v-gizhi-msft · Accepted solution]

Hi,

Please try this in Query Editor:

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 [#"DIRTY DATA" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DIRTY DATA", type text}}),
    KeyWordTable = Table.Buffer(#"Table 1"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let t = [DIRTY DATA] in  Table.SelectRows(Table.AddColumn(KeyWordTable,"New",each Text.Replace(t,[CODE COLUMN],"#(lf)")),each Text.Contains(t,[CODE COLUMN]))),
    #"Added Custom 2" = Table.AddColumn(#"Added Custom", "Custom2", each Table.ReorderColumns(Table.RenameColumns(Table.RemoveColumns(Table.AddColumn(Table.AddColumn(Table.SplitColumn([Custom], "New", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"New.1", "New.2"}), "fetched column1", each Text.End([New.1],1)), "fetched column3", each Text.Start([New.2],3)),{"New.1", "New.2"}),{{"CODE COLUMN", "fetched column2"}}),{"fetched column1", "fetched column2", "fetched column3"})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom 2",{"Custom"}),
    #"Expanded Custom2" = Table.ExpandTableColumn(#"Removed Columns", "Custom2", {"fetched column1", "fetched column2", "fetched column3"}, {"Custom2.fetched column1", "Custom2.fetched column2", "Custom2.fetched column3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom2",{{"Custom2.fetched column1", "fetched column1"}, {"Custom2.fetched column2", "fetched column2"}, {"Custom2.fetched column3", "fetched column3"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"DIRTY DATA"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","",null,Replacer.ReplaceValue,{"fetched column1","fetched column2","fetched column3"})
in
    #"Replaced Value"

 The result shows:

Here is my test pbix:

pbix 

Hope this can help.

Best Regards,

Giotto Zhi

[Ashish_Mathur]

Hi,

Write these calculated column formulas

Fetched column1 = =IFERROR(MID(Table1[DIRTY DATA],SEARCH([Fetched column2],Table1[DIRTY DATA])-1,1),BLANK())

Fetched column2 = =FIRSTNONBLANK(FILTER(VALUES(Table2[CODE COLUMN]),SEARCH(Table2[CODE COLUMN],Table1[DIRTY DATA],1,0)),1)

Fetched column3 = =if(ISBLANK(Table1[Fetched column2]),BLANK(),MID(Table1[DIRTY DATA],SEARCH([Fetched column2],Table1[DIRTY DATA])+LEN(Table1[Fetched column2]),3))

Write the Fetched column2 formula first.

Hope this helps.

[Nooby]

@Ashish_Mathur , thank you very much for your time.

I just copied "Fetched column2" as you suggested and pasted, nothing happens.

What am I doing wrong here?

[Nooby]

I just tried with "add new column" option from above, but I think I'm missing something:

[Ashish_Mathur]

Go to Modelling > New column.

[Ashish_Mathur]

Hi,

Mine is a DAX formula so write it as a calculated column formula.

[Nooby]

Thank you very much @Ashish_Mathur ,

It worked on report view like a charm.

But I cannot see that columns on "Power Query Editor". Is there a trick to use them there? 

[Ashish_Mathur]

Hi,

I do not know how to solve this problem in the Query Editor.