Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have data like this:
Account No. | Account Name | Selected Period |
Job 1 | ||
6-0600 | Bank Charges | 0 |
6-6800 | Rent & Outgoings | 0 |
6-8555 | Water | 0 |
Total Expense | 0 | |
Net Profit/(Loss) | 0 | |
Job 2 | ||
4-1000 | Sales | 237650 |
4-2000 | Freight Income | 0 |
Total Income | 237650 | |
5-1000 | Purchases | 137156.22 |
5-1300 | CONSUMABLES | 115.88 |
Total Cost Of Sales | 137272.1 | |
6-0600 | Bank Charges | 0 |
6-2600 | Freight & Couriers | 0 |
Total Expense | 0 | |
Net Profit/(Loss) | 100377.9 | |
Job 3 | ||
5-2700 | Freight / Landed Cost Adjustment | 0 |
Total Cost Of Sales | 0 | |
Net Profit/(Loss) | 0 | |
Would like to split it by job number into separate columns, with the listed columns B and C copying over, like below.
Account No. | Account Name | Selected Period | Account No. | Account Name | Selected Period | Account No. | Account Name | Selected Period |
Job 1 | Job 2 | Job 3 | ||||||
6-0600 | Bank Charges | 0 | 4-1000 | Sales | 237650 | 5-2700 | Freight / Landed Cost Adjustment | 0 |
6-6800 | Rent & Outgoings | 0 | 4-2000 | Freight Income | 0 | Total Cost Of Sales | 0 | |
6-8555 | Water | 0 | Total Income | 237650 | Net Profit/(Loss) | 0 | ||
Total Expense | 0 | |||||||
Net Profit/(Loss) | 0 | 5-1000 | Purchases | 137156.22 | ||||
5-1300 | CONSUMABLES | 115.88 | ||||||
Total Cost Of Sales | 137272.1 | |||||||
6-0600 | Bank Charges | 0 | ||||||
6-2600 | Freight & Couriers | 0 | ||||||
Total Expense | 0 | |||||||
Net Profit/(Loss) | 100377.9 |
Solved! Go to Solution.
Hi @Vanessa2 ,
Please try:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJbb4IwFID/SsPTlgi2ZYB7ROKSGRQztuyB+dBJBTZtTVuS7d+vFmEwTZaY9OHces53Lllmzfk7QNbIOr71KLN6om9DH0KtTQn7BFFJREGlVqHxzmtmG+8TZQq81RBiHyS1KnjFimGY52n1lSgqOrMWnrkiOzD7OlAmad+xpAqsBN9WanwTcylvf5NpWHwZ9s5G0OCkZGcosRv4XvMtPIgG9UHQqigVeGQbvqfnMJ2997lXw2trrGqxKYk0dZAbIM93MG5DXBMSJcv0ZRFO41l6DEKeM5kMi0VcKpBsQQusE+EAO+jKNbS9nTYR8VpUVMirJ65bdYPAue8G714e/IJ8DwHGICYsp3nTYJh/1FLt9Y2ck/ydwH8XsP4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account No." = _t, #"Account Name" = _t, #"Selected Period" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Account No.", type text}, {"Account Name", type text}, {"Selected Period", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if Text.Contains( [#"Account No."], "Job" ) then [Index] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}),
#"Grouped Rows" = Table.FromColumns(List.Zip(Table.Group(#"Removed Columns", {"Custom"},{{"Data", each Table.ToRecords(_)}})[Data])),
#"Transposed Table" = Table.Transpose(#"Grouped Rows"),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Transposed Table", "Column1", {"Account No.", "Account Name", "Selected Period"}, {"Account No.", "Account Name", "Selected Period"}),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column2", {"Account No.", "Account Name", "Selected Period"}, {"Account No..1", "Account Name.1", "Selected Period.1"}),
#"Expanded Column3" = Table.ExpandRecordColumn(#"Expanded Column2", "Column3", {"Account No.", "Account Name", "Selected Period"}, {"Account No..2", "Account Name.2", "Selected Period.2"})
in
#"Expanded Column3"
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group
Hi @Vanessa2 ,
Please try:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJbb4IwFID/SsPTlgi2ZYB7ROKSGRQztuyB+dBJBTZtTVuS7d+vFmEwTZaY9OHces53Lllmzfk7QNbIOr71KLN6om9DH0KtTQn7BFFJREGlVqHxzmtmG+8TZQq81RBiHyS1KnjFimGY52n1lSgqOrMWnrkiOzD7OlAmad+xpAqsBN9WanwTcylvf5NpWHwZ9s5G0OCkZGcosRv4XvMtPIgG9UHQqigVeGQbvqfnMJ2997lXw2trrGqxKYk0dZAbIM93MG5DXBMSJcv0ZRFO41l6DEKeM5kMi0VcKpBsQQusE+EAO+jKNbS9nTYR8VpUVMirJ65bdYPAue8G714e/IJ8DwHGICYsp3nTYJh/1FLt9Y2ck/ydwH8XsP4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account No." = _t, #"Account Name" = _t, #"Selected Period" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Account No.", type text}, {"Account Name", type text}, {"Selected Period", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if Text.Contains( [#"Account No."], "Job" ) then [Index] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}),
#"Grouped Rows" = Table.FromColumns(List.Zip(Table.Group(#"Removed Columns", {"Custom"},{{"Data", each Table.ToRecords(_)}})[Data])),
#"Transposed Table" = Table.Transpose(#"Grouped Rows"),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Transposed Table", "Column1", {"Account No.", "Account Name", "Selected Period"}, {"Account No.", "Account Name", "Selected Period"}),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column2", {"Account No.", "Account Name", "Selected Period"}, {"Account No..1", "Account Name.1", "Selected Period.1"}),
#"Expanded Column3" = Table.ExpandRecordColumn(#"Expanded Column2", "Column3", {"Account No.", "Account Name", "Selected Period"}, {"Account No..2", "Account Name.2", "Selected Period.2"})
in
#"Expanded Column3"
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group