Forum Discussion
Anonymous
3 years agoNot applicable
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 ...
- 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
ppm1
3 years agoSolution Sage
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
3 years agoNot applicable
Thank you so much Pat! It worked!