Transform date into correct order

[Anonymous]

I have to collect data from a website where the table is shown as given below:

Year/Month	Sales
2022-2023	2022-2023
January	1111
October	2222
September	3333
August	4444
July	5555
2021-2022	2021-2022
June	6666
May	7777
April	8888
March	9999
February	0000
January	1111
December	2222
November	3333
October	4444
September	5555
August	6666
July	7777
2020-2021	2020-2021
June	8888
May	9999

I will have to write a query to generate a monthly report. I need to transform the data as follows:

Year/Month	Sales
February-2023	0000
January-2023	1111
December-2022	2222
November-2022	3333

Could you kindly provide me a solution? Thanks in advance.

[ppm1 · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBNC4JAEIb/iuzZYF37PAbRIagOHcXDKksFprLtBv77xhkdV6j3IOzjuOPzZplQUqkFPNIoEvF0EnmciZOuvbYdvkkgCK+lawpjaRyC8GZaZ14jTiGI9/7u3w7ZEkKX+opuXEGQwL6kX6rGX6DTMF0bxGsIkrOmzzcQWtLaZ4VoCxlmbPlAtIMgOprCsoyE/Dc8mHJyYcVL8/lhGLbBivM22DNog224DdYBddn7J2MbdJq3EZh2gWf+BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Year/Month" = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year/Month", type text}, {"Sales", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Year/Month", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Custom", each if Text.Contains([#"Year/Month"], "-") then Text.Split([#"Year/Month"], "-") else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([#"Year/Month"], "-")),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "YearMonth", each if List.Contains({"January", "February", "March", "April", "May", "June"}, [#"Year/Month"]) then [#"Year/Month"] & " " & [Custom]{1} else [#"Year/Month"] & " " & [Custom]{0}),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"YearMonth", "Sales"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"YearMonth", type text}, {"Sales", Int64.Type}})
in
    #"Changed Type1"

Pat

[Anonymous]

Thank you so much Pat! It worked!