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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
AdrienPL
New Member

Power Query - Transform/consolidate columns Week number/year into columns Month number/year

Hello everyone , 

 

I have an excel file with in columns the format " Week number/ year "  but I would like (if possible) via query to get automatically these columns into format "Month number/year ".

 

Example :

From 

Column 1  | Column 2  | Column 3  | Column 4  | Column 5  | ...

W01/2023 | W02/2023 | W03/2023 | W04/2023 | W05/2023 | ....

10 pcs       | 10 pcs         | 10 pcs       | 10 pcs       | 60 pcs       | ...

 

* pcs = pieces

 

To 

Column 1  | Column 2  | ...

M01/2023 | M02/2023 | ....

40 pcs       | 60+...         | ...

 

Thanks in advance for your support.

1 REPLY 1
ppm1
Solution Sage
Solution Sage

You should unpivot your data for easier and more flexible analysis/visualization. Here is an example. Create a new blank query in the editor, open the Advanced Editor and replace the text there with this. This unpivots the week columns, splits at the / to get year and week columns and then adds a StartOfWeek column. You can then create a separate Date table (best practice) to have a relationship to the StartOfWeek column, so you can then visualize by week, month, year, etc..

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0ABJGIMLYQClWJ1rJCSRoAhI0ArGMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Wk01/2023" = _t, #"Wk03/2023" = _t, #"Wk06/2023" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Wk01/2023", Int64.Type}, {"Wk03/2023", Int64.Type}, {"Wk06/2023", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item"}, "Week", "Count"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Week", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, false), {"Week", "Year"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Week", type text}, {"Year", Int64.Type}}),
    #"Extracted Last Characters" = Table.TransformColumns(#"Changed Type1", {{"Week", each Text.End(_, 2), type text}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Extracted Last Characters",{{"Week", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "StartOfWeek", each Date.AddDays(#date([Year],1,1), 7 * [Week] - 1), type date),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Item", "StartOfWeek", "Count"})
in
    #"Removed Other Columns"

 

Pat

 

Microsoft Employee

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors
Top Kudoed Authors