Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am attempting to clean some data that has all the parent and child records in column A and the hierarchy is defined by the number of spaces. Example data:
I want to transpose each value in the "zone" column to the appropriate column based on the number of leading spaces (note that the column names and actual data will not have anything in common like they do in the example):
I also want to copy parent values down so that if it has a child record it is populated. Here is what the result would look like based on the example data above:
any help would be greatly appreciated, thanks in advance.
Solved! Go to Solution.
Hi, @gboldt ;
You could complete it in power query.
1.add conditional formula.
= Table.AddColumn(Source, "zone1", each if not Text.StartsWith([zone], " ") then [zone] else null)
= Table.AddColumn(#"Added Conditional Column", "unit", each if Text.StartsWith([zone]," ") and not Text.StartsWith([zone]," ") then [zone] else null)
= Table.AddColumn(#"Added Custom", "category", each if Text.StartsWith([zone]," ") and not Text.StartsWith([zone]," ") then [zone] else null)
= Table.AddColumn(#"Added Custom1", "Status", each if Text.StartsWith([zone]," ") and not Text.StartsWith([zone]," ") then [zone] else null)
2.fill down every column.
= Table.FillDown(#"Added Custom2",{"zone1"})
= Table.Group(#"Filled Down", {"zone1"}, {{"a", each Table.FillDown(_,{"unit"}), type table [zone=nullable text, zone1=text, unit=nullable text, category=nullable text, Status=nullable text]}})
= Table.Group(#"Expanded a", {"zone1", "unit"}, {{"category", each Table.FillDown(_,{"category"}), type table [zone1=text, unit=nullable text, category=nullable text, Status=nullable text]}})
The final show as follow:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqsrPS1VIVIrViVZSKM3LLDGEMBWSE0tS0/OLKhVgAgoKxSWJJaXFCKVG2JWCTExCqDJGVwXUFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [zone = _t]),
#"Added Conditional Column" = Table.AddColumn(Source, "zone1", each if not Text.StartsWith([zone], " ") then [zone] else null),
#"Added Custom" = Table.AddColumn(#"Added Conditional Column", "unit", each if Text.StartsWith([zone]," ") and not Text.StartsWith([zone]," ") then [zone] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "category", each if Text.StartsWith([zone]," ") and not Text.StartsWith([zone]," ") then [zone] else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Status", each if Text.StartsWith([zone]," ") and not Text.StartsWith([zone]," ") then [zone] else null),
#"Filled Down" = Table.FillDown(#"Added Custom2",{"zone1"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"zone1"}, {{"a", each Table.FillDown(_,{"unit"}), type table [zone=nullable text, zone1=text, unit=nullable text, category=nullable text, Status=nullable text]}}),
#"Expanded a" = Table.ExpandTableColumn(#"Grouped Rows", "a", {"unit", "category", "Status"}, {"unit", "category", "Status"}),
#"Grouped Rows1" = Table.Group(#"Expanded a", {"zone1", "unit"}, {{"category", each Table.FillDown(_,{"category"}), type table [zone1=text, unit=nullable text, category=nullable text, Status=nullable text]}}),
#"Expanded category" = Table.ExpandTableColumn(#"Grouped Rows1", "category", {"category", "Status"}, {"category", "Status"})
in
#"Expanded category"
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @gboldt ;
You could complete it in power query.
1.add conditional formula.
= Table.AddColumn(Source, "zone1", each if not Text.StartsWith([zone], " ") then [zone] else null)
= Table.AddColumn(#"Added Conditional Column", "unit", each if Text.StartsWith([zone]," ") and not Text.StartsWith([zone]," ") then [zone] else null)
= Table.AddColumn(#"Added Custom", "category", each if Text.StartsWith([zone]," ") and not Text.StartsWith([zone]," ") then [zone] else null)
= Table.AddColumn(#"Added Custom1", "Status", each if Text.StartsWith([zone]," ") and not Text.StartsWith([zone]," ") then [zone] else null)
2.fill down every column.
= Table.FillDown(#"Added Custom2",{"zone1"})
= Table.Group(#"Filled Down", {"zone1"}, {{"a", each Table.FillDown(_,{"unit"}), type table [zone=nullable text, zone1=text, unit=nullable text, category=nullable text, Status=nullable text]}})
= Table.Group(#"Expanded a", {"zone1", "unit"}, {{"category", each Table.FillDown(_,{"category"}), type table [zone1=text, unit=nullable text, category=nullable text, Status=nullable text]}})
The final show as follow:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqsrPS1VIVIrViVZSKM3LLDGEMBWSE0tS0/OLKhVgAgoKxSWJJaXFCKVG2JWCTExCqDJGVwXUFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [zone = _t]),
#"Added Conditional Column" = Table.AddColumn(Source, "zone1", each if not Text.StartsWith([zone], " ") then [zone] else null),
#"Added Custom" = Table.AddColumn(#"Added Conditional Column", "unit", each if Text.StartsWith([zone]," ") and not Text.StartsWith([zone]," ") then [zone] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "category", each if Text.StartsWith([zone]," ") and not Text.StartsWith([zone]," ") then [zone] else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Status", each if Text.StartsWith([zone]," ") and not Text.StartsWith([zone]," ") then [zone] else null),
#"Filled Down" = Table.FillDown(#"Added Custom2",{"zone1"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"zone1"}, {{"a", each Table.FillDown(_,{"unit"}), type table [zone=nullable text, zone1=text, unit=nullable text, category=nullable text, Status=nullable text]}}),
#"Expanded a" = Table.ExpandTableColumn(#"Grouped Rows", "a", {"unit", "category", "Status"}, {"unit", "category", "Status"}),
#"Grouped Rows1" = Table.Group(#"Expanded a", {"zone1", "unit"}, {{"category", each Table.FillDown(_,{"category"}), type table [zone1=text, unit=nullable text, category=nullable text, Status=nullable text]}}),
#"Expanded category" = Table.ExpandTableColumn(#"Grouped Rows1", "category", {"category", "Status"}, {"category", "Status"})
in
#"Expanded category"
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |