Need help new columns

Question

Hi, I have been searching for an answer to this for a while, cannot find a similar issue anywhere. Any help pointing me in the right direction would be eternally appreciated!

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

anonymous · Accepted Answer

Hi&nbsp;Vanessa2&nbsp;,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,GaoCommunity Support Team
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;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.&nbsp;Thanks a lot!
How to get your questions answered quickly&nbsp;--&nbsp;&nbsp;How to provide sample data in the Power BI Forum&nbsp;--&nbsp;China Power BI User Group

Forum Discussion

Need help new columns

1 Reply

Related content

New Column

Grouping rows by Column and then updating Column

Unpivot Column

Transform columns / type

Adding columns