Forum Discussion
How to Look up for values in another column based on current column value
Hi all,
I'm learning my way around Power Query. Hoping someone can help me with this.
I have a table that is similar to below.
| Col1 | Col2_link | Col3_link | Col4_link | Col5_link |
| URL1 | URL2 | URL3 | URL4 | |
| Col2 | URL5 | URL6 | URL7 | URL8 |
| Col4 | URL9 | URL10 | URL11 | URL12 |
| URL13 | URL14 | URL15 | URL16 |
In Col1, the values are either prefix of other column headers or blank. I want to know how to look up for the actual URL based on the column header prefix and leave the blanks as blank so it becomes
| Col1 | Col2_link | Col3_link | Col4_link | Col5_link |
| URL1 | URL2 | URL3 | URL4 | |
| URL5 | URL5 | URL6 | URL7 | URL8 |
| URL11 | URL9 | URL10 | URL111 | URL12 |
| URL13 | URL14 | URL15 | URL16 |
I tried the following but it's not working
= Table.ReplaceValue(#"PreviousStep", each if [Col1] <> "" then [[Col1]&"_link"] else [Col1])
Hi, Anonymous
You can paste the following M code to Advanced Editor to get the table you want.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRCg3yMYRQRhDKGEKZKMXqRCs55+dAhU0hlBmEModQFjBFJhC+JdRAAygNNdnQCKwOyoFaYAjVYwg12dBMKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2_link = _t, Col3_link = _t, Col4_link = _t, Col5_link = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2_link", type text}, {"Col3_link", type text}, {"Col4_link", type text}, {"Col5_link", type text}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Col2_link", "Col3_link", "Col4_link", "Col5_link"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom", each if Text.Contains([Attribute],[Col1]) and [Col1]<>"" then [Value] else ""),
Custom1 = Table.TransformRows(#"Added Custom",each
let
t=Text.Combine( Table.SelectRows(#"Added Custom",(x)=>x[Col1]=_[Col1])[Custom])
in
if _[Col1]<>"" and _[Custom]=""
then
[Col1=_[Col1],Attribute=_[Attribute],Value=_[Value],Custom=t]
else
_
),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Col1", "Attribute", "Value", "Custom"}, {"Col1", "Attribute", "Value", "Custom"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Col1"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
#"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 4), Int64.Type),
#"Removed Columns1" = Table.RemoveColumns(#"Inserted Integer-Division",{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Attribute]), "Attribute", "Value"),
#"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Integer-Division"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "Col1"}})
in
#"Renamed Columns"
The result looks like this:
Here is the sample.
Best Regards,
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
One way to do this is to create a calculated column in your Power Query
Thanks FarhanAhmed, the actual table I have do have more than 30 columns. I wonder if there is an easier way to do this.
In Excel, it would just be index(A:Z,row(A2),match(A2&"_link", 1:1,0))
Hi, Anonymous
You can paste the following M code to Advanced Editor to get the table you want.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRCg3yMYRQRhDKGEKZKMXqRCs55+dAhU0hlBmEModQFjBFJhC+JdRAAygNNdnQCKwOyoFaYAjVYwg12dBMKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2_link = _t, Col3_link = _t, Col4_link = _t, Col5_link = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2_link", type text}, {"Col3_link", type text}, {"Col4_link", type text}, {"Col5_link", type text}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Col2_link", "Col3_link", "Col4_link", "Col5_link"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom", each if Text.Contains([Attribute],[Col1]) and [Col1]<>"" then [Value] else ""),
Custom1 = Table.TransformRows(#"Added Custom",each
let
t=Text.Combine( Table.SelectRows(#"Added Custom",(x)=>x[Col1]=_[Col1])[Custom])
in
if _[Col1]<>"" and _[Custom]=""
then
[Col1=_[Col1],Attribute=_[Attribute],Value=_[Value],Custom=t]
else
_
),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Col1", "Attribute", "Value", "Custom"}, {"Col1", "Attribute", "Value", "Custom"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Col1"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
#"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 4), Int64.Type),
#"Removed Columns1" = Table.RemoveColumns(#"Inserted Integer-Division",{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Attribute]), "Attribute", "Value"),
#"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Integer-Division"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "Col1"}})
in
#"Renamed Columns"
The result looks like this:
Here is the sample.
Best Regards,
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
