Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
makabirarif
Frequent Visitor

Transform date into correct order

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

Year/MonthSales
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.

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution 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

Microsoft Employee

View solution in original post

2 REPLIES 2
ppm1
Solution Sage
Solution 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

Microsoft Employee

Thank you so much Pat! It worked!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors