How to Look up for values in another column based on current column value

[Anonymous]

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])

[v-cazheng-msft · Accepted solution]

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.

[FarhanAhmed]

One way to do this is to create a calculated column in your Power Query 

[Anonymous]

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))