Notifications
Clear all
General Excel Questions & Answers
2
Posts
2
Users
0
Reactions
193
Views
Topic starter
Hi, I am looking for some help in unpivoting data in the attached excel file. It is a small sample. Any help would be appreciated. Thank you!
Posted : 20/10/2024 1:10 am
I assume you are somewhat familiar with Power Query. Then the script I have pasted below is a very basic way of unpivoting and transforming the data into something that you could use in a pivot table. Just point the Source step to the correct location on your own computer. If this doesn't work for you, come back here.
let
Source = Excel.Workbook(File.Contents("/........./General-Ledger.xlsx"), null, true),
#"Navigation 1" = Source{[Item = "General Ledger Detail", Kind = "Sheet"]}[Data],
#"Promoted headers" = Table.PromoteHeaders(#"Navigation 1", [PromoteAllScalars = true]),
#"Removed blank rows" = Table.SelectRows(#"Promoted headers", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Inserted conditional column" = Table.AddColumn(#"Removed blank rows", "Account", each if [Source] = null then [Date] else null),
#"Filled down" = Table.FillDown(#"Inserted conditional column", {"Account"}),
#"Filtered rows" = Table.SelectRows(#"Filled down", each ([Source] <> null)),
#"Unpivoted other columns" = Table.UnpivotOtherColumns(#"Filtered rows", {"Account", "Date", "Source", "Reference"}, "D/C/Gst", "Amount"),
#"Filtered rows 1" = Table.SelectRows(#"Unpivoted other columns", each ([Amount] <> 0)),
#"Added custom" = Table.AddColumn(#"Filtered rows 1", "Amount2", each if [#"D/C/Gst"] = "Credit" then [Amount]*-1 else [Amount]),
#"Changed column type" = Table.TransformColumnTypes(#"Added custom", {{"Date", type date}, {"Source", type text}, {"Reference", type text}, {"Account", type text}, {"D/C/Gst", type text}, {"Amount", Currency.Type}, {"Amount2", Currency.Type}})
in
#"Changed column type"
Posted : 20/10/2024 3:25 am
