Forum Discussion
Pivot Columns with DAX
Greetings, Power BI pros. I'm trying to break a time series SPC chart into stages based on a list of stage start dates that will be added to over time. The end result I'm looking for looks like this:
The way I accomplished this in Excel was with a pivot table that has week starting on rows, and stage on columns, so each time series breaks into one column for each stage (in the case below, 3), like so:
I'd like to recreate this report using Power BI desktop, but I don't know how to break the single time series column into one column for each stage. Is there a DAX pivot pattern? I know that PowerQuery has pivot capabilities, but I am trying to avoid that. The key benefit of the pivot in Excel is that it updates properly when the user adds additional stages without any additional modification. Thanks!
8 Replies
Hi GDUb on the link below you can find list of Statistical function, go to single one and check example.
https://dax.guide/functions/statistical/
Did I answer your question? Kudos appreciated / accept solution!
Thanks, @somebih. Sorry for the confusion. In a nutshell, what I'm trying to do is pivot a row to a column with DAX, turning something like this:
Planning Week Stage Sample Data 8/8/2022 1 0.01% 8/15/2022 1 0.00% 8/22/2022 1 0.29% 8/29/2022 1 0.20% 9/5/2022 1 -0.02% 9/12/2022 1 0.03% 9/19/2022 1 -0.05% 9/26/2022 1 -0.01% 10/3/2022 1 0.22% 10/10/2022 1 -0.03% 10/17/2022 1 0.00% 10/24/2022 1 -0.03% 10/31/2022 1 -0.34% 11/7/2022 2 0.09% 11/14/2022 2 -0.06% 11/21/2022 2 0.14% 11/28/2022 2 0.89% 12/5/2022 2 -0.16% 12/12/2022 2 0.41% 12/19/2022 2 -0.38% 12/26/2022 2 -0.01% 1/2/2023 2 0.32% 1/9/2023 3 0.14% 1/16/2023 3 -0.32% 1/23/2023 3 -0.02% 1/30/2023 3 -0.18% 2/6/2023 3 0.02% 2/13/2023 3 0.06% 2/20/2023 3 -0.00% 2/27/2023 3 -0.01% 3/6/2023 3 -0.01% Into this:
Stage Planning Week 1 2 3 8/8/2022 0.01% 8/15/2022 0.00% 8/22/2022 0.29% 8/29/2022 0.20% 9/5/2022 -0.02% 9/12/2022 0.03% 9/19/2022 -0.05% 9/26/2022 -0.01% 10/3/2022 0.22% 10/10/2022 -0.03% 10/17/2022 0.00% 10/24/2022 -0.03% 10/31/2022 -0.34% 11/7/2022 0.09% 11/14/2022 -0.06% 11/21/2022 0.14% 11/28/2022 0.89% 12/5/2022 -0.16% 12/12/2022 0.41% 12/19/2022 -0.38% 12/26/2022 -0.01% 1/2/2023 0.32% 1/9/2023 0.14% 1/16/2023 -0.32% 1/23/2023 -0.02% 1/30/2023 -0.18% 2/6/2023 0.02% 2/13/2023 0.06% 2/20/2023 0.00% 2/27/2023 -0.01% 3/6/2023 -0.01% I'm looking for a general solution where the number of stages is changing/unknown, so a brute force formula that explicitly names the stages, such as the one suggested at Solved: Re: Convert multiple column data into a column wit... - Microsoft Fabric Community, will not suffice.
Thanks!
Hi GDUb after I inserted data into Excel file, imported into pbi file, in Power Query I did transformation as on Pic1, then on tab Transform choose as on Pic 2 and final step is Pict 3.
Did I answer correctly? Kudos appreciate / accept solution.
Pic 1
Pict 2
Pic 3 final (you can replace and rename as you wish)
