Forum Discussion
Power Query unpivot or anything that can solve the problem
Hi All,
I have the data below and I want to separate the date data from other figures so as to have a separate date column. What is the best way to achieve this? I'm stuck in power query trying to resolve this.
| Financial Metrics | Column2 | Column3 | Column4 | Column5 | Column6 |
| 01/01/1900 | 01/04/2019 | 01/05/2019 | 01/06/2019 | 01/07/2019 | 01/08/2019 |
| Property Charge Total | 132849762 | 132654771.4 | 132689500 | 132930634 | 132935102 |
| Less Benefits | 11264001 | 11302042 | 11279919 | 11294336 | 11289286 |
| Net Debit | 121585761 | 121352729.4 | 121409581 | 121636298 | 121645816 |
| Payments | 13482264 | 25052747 | 36382988 | 47889679 | 58318724 |
| add Transfers | 929341 | 927288 | 925194 | 927182 | 927244 |
| less Refunds | 91545 | 153007 | 229767 | 321794 | 344355 |
| less Overpayments | 411869 | 460801 | 489362 | 528858 | 548867 |
| less paid to costs | 0 | 7481 | 16676 | 32580 | 39944 |
| net Payments | 13908191 | 25358746 | 36572377 | 47933629 | 58312802 |
| % Collected | 11.43899654 | 20.89672489 | 30.12313913 | 39.40733958 | 47.93654555 |
| 01/01/1900 | 10/10/2019 | 10/11/2019 | 10/12/2019 | 13/10/2019 | 14/10/2019 |
| Property Charge Total | 133083804.2 | 133075873.8 | 133071494.4 | 133071494.4 | 133071494.4 |
| Less Benefits | 11231565.58 | 11230588.78 | 11223782.32 | 11223325.35 | 11223325.35 |
| Net Debit | 121852238.6 | 121845285 | 121847712 | 121848169 | 121848169 |
| Payments | 81431381.71 | 81518504.86 | 81518416.89 | 81518416.89 | 81607336.36 |
| add Transfers | 927610.84 | 927610.74 | 927610.74 | 927610.74 | 927610.74 |
| less Refunds | 561253.02 | 566461.92 | 576756.6 | 576756.6 | 576756.6 |
| less Overpayments | 658441.64 | 660007.51 | 653572.98 | 653650.72 | 654677.06 |
| less paid to costs | 90979.15 | 93038.7 | 93038.7 | 93038.7 | 94229.2 |
| net Payments | 81048318.74 | 81126607.47 | 81122659.35 | 81122581.61 | 81209284.24 |
| % Collected | 66.51360669 | 66.58165513 | 66.57708872 | 66.57677524 | 66.64793152 |
| 01/01/1900 | 11/01/2019 | 11/04/2019 | 11/05/2019 | 11/06/2019 | 11/07/2019 |
12 Replies
Hi Anonymous ,
Try this example query. It transforms your data into the most efficient reporting structure:
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]), // Relevant steps ----> transposeTable = Table.Transpose(Source), promHeaders = Table.PromoteHeaders(transposeTable, [PromoteAllScalars=true]), unpivOthCols = Table.UnpivotOtherColumns(promHeaders, {"01/01/1900"}, "ValueType", "Value"), // <---- Relevant steps renDateCol = Table.RenameColumns(unpivOthCols,{{"01/01/1900", "Date"}}) in renDateColExample output:
You can obviously clean up the "_x" suffixes etc., but these few steps get you to the correct structure and fix the Date issue.
Pete
Hi BA_Pete,
Thanks again for the solution you provided. I have encountered issue trying to get the code to work in the rest of the data as I only provided sample data from the whole data in my post. When I used the code, it only showed me the data for 2019 as I provided. How do I structure the code to look at the real source in order to show the rest of the data?
ThanksYou just need to swap the "source" section in the screenshot below for the steps that constitute your source, then change the Source reference in the Table.Transpose function to the name of the step that precedes it:
Pete
Hi BA_Pete,
Sorry for my questions.
This is the original m code from the source. How do I change it to fit in?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"})
in
#"Replaced Value"
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.
