Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 |
Solved! Go to 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"
Proud to be a Super User! | |
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
Thank you so much I will try that 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"
Proud to be a Super User! | |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
62 | |
18 | |
16 | |
13 |