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.