Forum Discussion
Power Query unpivot or anything that can solve the problem
Hi.
Below are M code for the above request with seperate date column,Financial Metrics and its value.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVVNb9swDP0rRoHdClX8FHVddxy2Yuit6CFr3K1AlhRJNqD/fhTlNG6RDAN8eE+yaOqRj767u8hw5Q/UnC8ug/AVZqgTkTnROSlzYp3cX95d3Gw3z+N2/zJc/1xsf4zD7Wa/WPlrQGhci2LHKlwKJJ6YVYkEHFfKSnzAAhkj7udxtxs+juvx8Wm/a7uAyjlDQMqYGftqqTXycliZSDu0iqYR6Mu4Hz6N35/2bQNBTIpCxyRYsPakEDhXsWlHSbHahNmXe6ybxcuvcd3zITb0lByiZA/ExSEpmR9sJ7mYVS0tNTECK8gRY7FcDrfbxXr3OG5boOq3ZghQME5WFKjcV8Bw2uJ+fNV0+TY+/l4v4zQIS0tHKOeWAaKLHqkglIhCzCRyPPz1z7h9Pl6EAUxblqzZQl+2SlE38XykZSRs5kFfQzwvnpbDfjM8bHYRo5WycBdPtWh8XqwtU61T5msvxBsBazaoEAKSWOE4plKQSgkBK7UyTAKiTZ3xYbjerFbjw35cRq0Tk9XqDdYi5dRER79CC5YTIPmHgCKTxLkQVenlSX5LF2+S5o0vIF/5MzV8IzAn+Epo/hq/kn/7grKRZU44seJ3p2QHBlx58sk5dsYfBKKS4naNZTFLZWKuqWEiPDAvTyJ5x07YxcS3LenE2FtCDtgNjQfsDqlv8Du7GLAXwiAVCCYe2TUwPTAGTVG090xbzTSRnnGPu9mLzkdc/g+fMJMoeCumHL2vygqpBnZHiYYIp+A5Z6kYM6SYEarZ/ZkEYp28yVPMF8cqng8GZi0lZT1vtJprqQlaCXxuemHKOcQ+CBKeMp5B5jaQuiDWBqtrnGJ+NeazuvbeCObDL2kvGmafqt64fMKHqn450qzRB4156UTCeY2Vks36NRvziwp2YVwgdzoInvBhkIPB5j8rmP+sYP6zguPP6v7+Lw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Financial Metrics" = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Financial Metrics", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","01/01/1900","Date",Replacer.ReplaceText,{"Financial Metrics"}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Replaced Value",1),
#"Transposed Table" = Table.Transpose(#"Removed Bottom Rows"),
#"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
#"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged.1"),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less Refunds:less Overpayments:less paid to costs:net Payments:% Collected", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less Refunds:less Overpayments:less paid to costs:net Payments:% Collected", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less R", "Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less.1", "Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less.2", "Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less.3", "Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less.4", "Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less.5", "Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less.6", "Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less.7", "Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less.8", "Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less.9", "Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:les.10"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less R", type text}, {"Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less.1", type text}, {"Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less.2", type text}, {"Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less.3", type text}, {"Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less.4", type text}, {"Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less.5", type text}, {"Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less.6", type text}, {"Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less.7", type text}, {"Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less.8", type text}, {"Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:less.9", type text}, {"Date:Property Charge Total:Less Benefits:Net Debit:Payments:add Transfers:les.10", type text}}),
#"Promoted Headers1" = Table.PromoteHeaders(#"Changed Type2", [PromoteAllScalars=true]),
#"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Date", type date}, {"Property Charge Total", type number}, {"Less Benefits", type number}, {"Net Debit", type number}, {"Payments", type number}, {"add Transfers", type number}, {"less Refunds", type number}, {"less Overpayments", type number}, {"less paid to costs", type number}, {"net Payments", type number}, {"% Collected", type number}}),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Changed Type3", {"Date"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns1",{{"Attribute", "Financial Metrics"}})
in
#"Renamed Columns"
Thanks Subash_Govind and BA_Pete,
I have been able to get it to work to some extent but still having issue with it.
The issue that I have is that it is only transposing the very first row. If you look at my first example data, you will notice that after % collected, it would start another header with different dates. Those dates are what I'm trying to put together in one column and not just the first row dates.
Share some desired output for reference.
Anonymous I believe the very last row of your sample (with dates) is from next group of data. If so, try this
let Source = Excel.Workbook(File.Contents("C:\Users\ejye\OneDrive\OneDrive - elegant\Desktop\BVPI Calc SS Automation.xlsx"), null, false), #"Removed Other Columns" = Table.SelectColumns(Source,{"Data"}), #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}), #"Removed Blank Rows" = Table.SelectRows(#"Expanded Data", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))), #"Renamed Columns" = Table.RenameColumns(#"Removed Blank Rows",{{"Column1", "Financial Metrics"}}), #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,"01/01/1900",Replacer.ReplaceValue,{"Financial Metrics"}), // here goes the code: split = Table.Split(#"Replaced Value", 11), combine = Table.Combine( List.Transform( split, (x) => [tr = Table.Transpose(x), h = Table.PromoteHeaders(tr)][h] ) ) in combineHi Anonymous ,
It seems that Subash_Govind and AlienSx really want to help you on this, so I'm going to leave it with them now.
All the best 👍
Pete
Thanks everyone for your help.
I have been able to get all the dates to be in one column using the split code from Subash_Govind.
I will now work on the data to see how far I can get. It's been a jpourney trying to get an already produced data to be a raw data kind of.
