Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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:
Hope this can help.
Best Regards,
Giotto Zhi
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
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?
I just tried with "add new column" option from above, but I think I'm missing something:
Go to Modelling > New column.
Hi,
Mine is a DAX formula so write it as a calculated column formula.
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?
Hi,
I do not know how to solve this problem in the Query Editor.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |