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
Hello all,
Can anyone help with the following scenerio?
My columns in Power Query look something like this:
And I want to look like this:
Any help would be greatly appreciated!
Solved! Go to Solution.
Hi @LoredanaC, try this
Result
v1
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WiskLKUpMLskvislT0lFyS81LKYnJC0vMKSlKjMlzTixOVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Machinery = _t, Type = _t]),
ToList = Table.TransformColumns(Source, List.Transform({"Machinery", "Type"}, (colName)=> {colName, each Text.SplitAny(_, "#(lf), #(cr)") } )),
Ad_Merged = Table.AddColumn(ToList, "Merged", each List.Transform(List.Zip({ [Machinery], [Type] }), (x)=> Text.Combine(x, "|")), type list),
RemovedColumns = Table.RemoveColumns(Ad_Merged,{"Machinery", "Type"}),
ExpandedMerged = Table.ExpandListColumn(RemovedColumns, "Merged"),
SplitColumnByDelimiter = Table.SplitColumn(ExpandedMerged, "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Machinery", "Type"}),
TrimmedText = Table.TransformColumns(SplitColumnByDelimiter,{{"Machinery", Text.Trim, type text}}),
ReplacedValue = Table.ReplaceValue(TrimmedText,"",null,Replacer.ReplaceValue,{"Machinery"}),
FilledDown = Table.FillDown(ReplacedValue,{"Machinery"}),
FilledUp = Table.FillUp(FilledDown,{"Machinery"})
in
FilledUp
v2 (won't work properly if you have more columns than these two. In that case query needs to be updated)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WiskLKUpMLskvislT0lFyS81LKYnJC0vMKSlKjMlzTixOVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Machinery = _t, Type = _t]),
Transformed = Table.FromRows(List.TransformMany(Table.ToRows(Source),
each List.Transform(List.Skip(_), (a)=> List.Select(Text.SplitAny(a, "#(lf), #(cr)"), (b)=> Text.Trim(b) <> "")),
(x,y)=> {Text.Trim(x{0}, {"#(lf)", "#(cr)"})} & {y} ), {"Machinery", "Type"}),
ExpandedType = Table.ExpandListColumn(Transformed, "Type")
in
ExpandedType
Hi @LoredanaC, try this
Result
v1
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WiskLKUpMLskvislT0lFyS81LKYnJC0vMKSlKjMlzTixOVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Machinery = _t, Type = _t]),
ToList = Table.TransformColumns(Source, List.Transform({"Machinery", "Type"}, (colName)=> {colName, each Text.SplitAny(_, "#(lf), #(cr)") } )),
Ad_Merged = Table.AddColumn(ToList, "Merged", each List.Transform(List.Zip({ [Machinery], [Type] }), (x)=> Text.Combine(x, "|")), type list),
RemovedColumns = Table.RemoveColumns(Ad_Merged,{"Machinery", "Type"}),
ExpandedMerged = Table.ExpandListColumn(RemovedColumns, "Merged"),
SplitColumnByDelimiter = Table.SplitColumn(ExpandedMerged, "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Machinery", "Type"}),
TrimmedText = Table.TransformColumns(SplitColumnByDelimiter,{{"Machinery", Text.Trim, type text}}),
ReplacedValue = Table.ReplaceValue(TrimmedText,"",null,Replacer.ReplaceValue,{"Machinery"}),
FilledDown = Table.FillDown(ReplacedValue,{"Machinery"}),
FilledUp = Table.FillUp(FilledDown,{"Machinery"})
in
FilledUp
v2 (won't work properly if you have more columns than these two. In that case query needs to be updated)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WiskLKUpMLskvislT0lFyS81LKYnJC0vMKSlKjMlzTixOVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Machinery = _t, Type = _t]),
Transformed = Table.FromRows(List.TransformMany(Table.ToRows(Source),
each List.Transform(List.Skip(_), (a)=> List.Select(Text.SplitAny(a, "#(lf), #(cr)"), (b)=> Text.Trim(b) <> "")),
(x,y)=> {Text.Trim(x{0}, {"#(lf)", "#(cr)"})} & {y} ), {"Machinery", "Type"}),
ExpandedType = Table.ExpandListColumn(Transformed, "Type")
in
ExpandedType
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 |
---|---|
68 | |
68 | |
27 | |
18 | |
12 |