How to transform data

[mofu1401]

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.

7March.xlsx

Thank all in advance.

[Ritaf1983]

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

[Greg_Deckler · Accepted solution]

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

[johnt75]

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.