Forum Discussion
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 | 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.
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
2 Replies
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