I need help with Power Query solution (code or transformation)

[TomaszTa]

I need to create code or solution in Power Query becaus this will used in next ransformations. Any help is greately apreciated!
Where "WER" = Yes then Find value from ABC and Populate where EFG is the same.

ABC	EFG	WER	VG (Leader)	Solution (Group Leader)
qwew	ab	No		xyzacd
wwd	ab	No		xyzacd
sffd	ab	No		xyzacd
afedfdgdg	ab	No		xyzacd
xyzacd	ab	Yes	xyzacd	xyzacd
dfddfg	ab	No		xyzacd
rfdsdd	ss	No		grftrt
jhjhdfjd	ss	No		grftrt
grftrt	ss	Yes	grftrt	grftrt
sfdgfdgfd	ss	No		grftrt
eewewx	rg	No		Null
dfsdfd	rg	No		null
fghsr	rg	No		Null
sdsdg	ff	No		Null
dfdff	ff	No		Null

[dufoq3]

Hi @TomaszTa, different approach.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDBCsMgEER/pXjOt/ReJIe04yheSt2Aab++q0kLhgUZ8D3GXfTevWqobnLLXeP61Li4efKuVhhUSAsvDCAiouG292d5/Eu3IJoHa1p7oNUrhKD1REaRU05gtlQsXMv6E/usg+3bI/ZjVEPQf9j0WuIoQEFvnAVjkmJw0b0bIs8PoaOBz18=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ABC = _t, EFG = _t, WER = _t, #"VG (Leader)" = _t]),
    Ad_Solution = Table.AddColumn(Source, "Solution (Group Leader)", each if [WER] = "Yes" then [#"VG (Leader)"] else null, type text),
    GroupedRows = Table.Group(Ad_Solution, {"EFG"}, {{"All", each Table.FillUp(Table.FillDown(_, {"Solution (Group Leader)"}), {"Solution (Group Leader)"}), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

[TomaszTa]

Thank you so much I will try that solution.

[jgeddes · Accepted solution]

Here is one possible solution to your question.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZDRDsIgDEV/xfC8b9m7IXtAL2Uhi0Y6w/Tr7RCVJa4JNKTncEOx1tyyz6Yz7iSlv0o5yF4eT3eGGTprcoaGmUjljjwIAUGT6rEaR88/vPEkCaQmJQJjvcXcGiHRnOZixDGOoKg69ViN93u+vcZjmawsLcx7+eFFeim0Rn+fpjoUoyRs+eXDKYyc9q+zDLwSop10FPIPDy8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ABC = _t, EFG = _t, WER = _t, #"VG (Leader)" = _t, #"Solution (Group Leader)" = _t]),
    #"Changed Type" = 
    Table.TransformColumnTypes(
        Source,
        {{"ABC", type text}, {"EFG", type text}, {"WER", type text}, {"VG (Leader)", type text}, {"Solution (Group Leader)", type text}}
    ),
    #"Grouped Rows" = 
    Table.Group(
        #"Changed Type", 
        {"EFG"}, 
        {{"_nestedTable", each _, type table [ABC=nullable text, EFG=nullable text, WER=nullable text, #"VG (Leader)"=nullable text, #"Solution (Group Leader)"=nullable text]}}
    ),
    fxLeaderValue = (inputTable as table) =>
    let 
        rows = Table.SelectColumns(Table.SelectRows(inputTable, each [WER]= "Yes"), {"ABC"}),
        value = Table.AddColumn(inputTable, "Solution", each Table.FirstValue(rows))
    in 
        value,
    Custom1 = 
    Table.TransformColumns(
        #"Grouped Rows", 
        {
            {"_nestedTable", each fxLeaderValue(_)}
        }
    ),
    #"Expanded _nestedTable" = 
    Table.ExpandTableColumn(
        Custom1, 
        "_nestedTable", 
        {"ABC", "WER", "VG (Leader)", "Solution"}, 
        {"ABC", "WER", "VG (Leader)", "Solution"}
    )
in
    #"Expanded _nestedTable"