Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Transform date into correct order

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   ...
  • ppm1's avatar
    3 years ago

    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