Power Query unpivot or anything that can solve the problem

[Anonymous]

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

[Subash_Govind]

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"

[Anonymous]

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. 

[BA_Pete]

Hi @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

[Anonymous]

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. 

[AlienSx]

@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
    combine

[Subash_Govind]

Share some desired output for reference.

[Anonymous]

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?

Thanks

[BA_Pete]

You 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

[Anonymous]

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"

[BA_Pete]

Ok, so assuming that your final #"Replaced Value" step gets us to exactly the same place as the example data, you would add my steps on the end like 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"}),
	
    transposeTable = Table.Transpose(#"Replaced Value"),  // <--- Notice previous step name changed
    promHeaders = Table.PromoteHeaders(transposeTable, [PromoteAllScalars=true]),
    unpivOthCols = Table.UnpivotOtherColumns(promHeaders, {"01/01/1900"}, "ValueType", "Value"),
    renDateCol = Table.RenameColumns(unpivOthCols,{{"01/01/1900", "Date"}})

in
    renDateCol

Pete

[BA_Pete]

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
    renDateCol

Example 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

[Anonymous]

Thanks Pete,

I have been battling this since yesterday and only now resolved to get it manually done after I noticed that transpose in Power query can help me to clean it up a bit but this code simplifies the manual work for me and it is working for the sample dataset that I have tested it on.