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

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.