Forum Discussion
How to transform data
Hi all,
I have some old data in this format, is there a way to transform the data?
I tried but I can't seem to transform it to show that Jan to Apr belongs the next year, 2014.
Thank all in advance.
mofu1401 Try this, transformed a single table:
let Source = Excel.Workbook(File.Contents("C:\Users\gdeck\Downloads\7March.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Period", type text}, {"May", Int64.Type}, {"Jun", Int64.Type}, {"Jul", Int64.Type}, {"Aug", Int64.Type}, {"Sep", Int64.Type}, {"Oct", Int64.Type}, {"Nov", Int64.Type}, {"Dec", Int64.Type}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}, {"Total", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Period"}, "Attribute", "Value"), #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Other Columns", "Period", "Period - Copy"), #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Period - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Period - Copy.1", "Period - Copy.2", "Period - Copy.3", "Period - Copy.4", "Period - Copy.5"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Period - Copy.1", type text}, {"Period - Copy.2", Int64.Type}, {"Period - Copy.3", type text}, {"Period - Copy.4", type text}, {"Period - Copy.5", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Period - Copy.1", "Period - Copy.3", "Period - Copy.4"}), #"Added Custom" = Table.AddColumn(#"Removed Columns", "Year", each if [Attribute] = "Jan" or [Attribute] = "Feb" or [Attribute] = "Mar" or [Attribute] = "Apr" then [#"Period - Copy.5"] else [#"Period - Copy.2"]), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Period - Copy.2", "Period - Copy.5"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Attribute", "Month"}}) in #"Renamed Columns"
3 Replies
You can make 2 reference copies of the table, choosing the columns from 1 year in 1 and from the second year in the other. Unpivot the columns and get the appropriate year from the Period column, then append the queries back together. See the attached.
mofu1401 Try this, transformed a single table:
let Source = Excel.Workbook(File.Contents("C:\Users\gdeck\Downloads\7March.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Period", type text}, {"May", Int64.Type}, {"Jun", Int64.Type}, {"Jul", Int64.Type}, {"Aug", Int64.Type}, {"Sep", Int64.Type}, {"Oct", Int64.Type}, {"Nov", Int64.Type}, {"Dec", Int64.Type}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}, {"Total", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Period"}, "Attribute", "Value"), #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Other Columns", "Period", "Period - Copy"), #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Period - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Period - Copy.1", "Period - Copy.2", "Period - Copy.3", "Period - Copy.4", "Period - Copy.5"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Period - Copy.1", type text}, {"Period - Copy.2", Int64.Type}, {"Period - Copy.3", type text}, {"Period - Copy.4", type text}, {"Period - Copy.5", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Period - Copy.1", "Period - Copy.3", "Period - Copy.4"}), #"Added Custom" = Table.AddColumn(#"Removed Columns", "Year", each if [Attribute] = "Jan" or [Attribute] = "Feb" or [Attribute] = "Mar" or [Attribute] = "Apr" then [#"Period - Copy.5"] else [#"Period - Copy.2"]), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Period - Copy.2", "Period - Copy.5"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Attribute", "Month"}}) in #"Renamed Columns"Hi mofu1401
You can use the M code after importing your table :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZAxDsMgDEWvgjxniA0BMvYAPUHE0BM0irr09uX/yCnKwJex8bM/2ybP1zfYrDF83uGxH4iTTMIzdzFIRpA9FbWLMrfiSomQxGqVNl3gNICXXq7oJQU3kKKBpC4M/qnoY8zSyF0GLlfBbNVLki9K/OovMN4KxOs3bh64xduzb5I9c1olFj7oHq7Yc35cLdLaDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, May = _t, Jun = _t, Jul = _t, Aug = _t, Sep = _t, Oct = _t, Nov = _t, Dec = _t, Jan = _t, Feb = _t, Mar = _t, Apr = _t, Total = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type text}, {"May", Int64.Type}, {"Jun", Int64.Type}, {"Jul", Int64.Type}, {"Aug", Int64.Type}, {"Sep", Int64.Type}, {"Oct", Int64.Type}, {"Nov", Int64.Type}, {"Dec", Int64.Type}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}, {"Total", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Period"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Month"}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Period", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Period.1", "Period.2", "Period.3", "Period.4", "Period.5"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Period.1", type text}, {"Period.2", Int64.Type}, {"Period.3", type text}, {"Period.4", type text}, {"Period.5", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Period.1", "Period.3", "Period.4"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Period.2", "Min Year"}, {"Period.5", "Max year"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns1", each ([Month] <> "Total")),
#"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Custom", each if [Month] = "May" then 5 else if [Month] = "Jun" then 6 else if [Month] = "Jul" then 7 else if [Month] = "Aug" then 8 else if [Month] = "Sep" then 9 else if [Month] = "Oct" then 10 else if [Month] = "Nov" then 11 else if [Month] = "Dec" then 12 else if [Month] = "Jan" then 1 else if [Month] = "Feb" then 2 else if [Month] = "Mar" then 3 else if [Month] = "Apr" then 4 else null, type number),
#"Renamed Columns2" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Month Number"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns2", "Year", each if [Month Number]>=5 and [Month Number]<=12 then [Min Year]
else
[Max year]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Year", Int64.Type}}),
#"Added Custom Column" = Table.AddColumn(#"Changed Type2", "Custom", each Text.Combine({Text.Middle(Text.From([Year], "en-GB"), 1, 2), "/", Text.PadStart(Text.From([Month Number], "en-GB"), 2, "0"), "/", Text.From([Year], "en-GB")}), type text),
#"Inserted Parsed Date" = Table.AddColumn(#"Added Custom Column", "Parse", each Date.From(DateTimeZone.From([Custom])), type date),
#"Removed Columns1" = Table.RemoveColumns(#"Inserted Parsed Date",{"Custom"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns1",{{"Parse", "Date"}}),
#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns3",{"Min Year", "Max year"})
in
#"Removed Columns2"Or follow my steps from UI of PQ :
Pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
