Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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
Check out the July 2025 Power BI update to learn about new features.