Forum Discussion
Solved
Fetching a column values from another column
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!
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:
Hope this can help.
Best Regards,
Giotto Zhi
8 Replies
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.
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?
Hi,
Mine is a DAX formula so write it as a calculated column formula.
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:
Hope this can help.
Best Regards,
Giotto Zhi
