Transpose & Pivot using Power Query

hi arimoldi ,

select the ACTIVITY column and unpivot other columns.

more about unpivot:

https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098#:~:text=This%20command%20unpivots%20unselected%20columns.%20Use%20this%20command,press%20Shift%2BClick%20or%20CTRL%2BClick%20on%20each%20subsequent%20column.

Yes you can do this with the help of Pivot and Unpivot data in power query.

1.Go to Power BI Power Query Editor.

2.Then go to Transform Option.

3. Under Any Column Group there are many opitons but two are for you a: Unpivot Columns, b: Pivot Coumn.

4. Unpitovt Columns will solve your issue.

Please follow below detail:

1. Load the Data into Power Query:

   • Load your data into Power Query Editor by selecting your data range and choosing "Data" > "From Table/Range" in Excel (make sure your data is in a table format).

2. Unpivot the Data:

   • In Power Query Editor, select the date columns (e.g., 01/10/2024, 02/10/2024, etc.).
   • Go to the Transform tab and select Unpivot Columns. This will create three columns: ACTIVITY, Attribute, and Value.

3. Rename Columns:

   • Rename the Attribute column to DATE.
   • Rename the Value column to NUM.

4. Set Data Types:

   • Ensure that the DATE column is set to the Date data type, and the NUM column is set to the Whole Number data type (or Decimal, depending on your data).

5. Sort (Optional):

   • You may want to sort the table by DATE and then by ACTIVITY to get the desired order.

6. Close & Load:

   • Click Close & Load to load the transformed data back into Excel or Power BI.

Result

You should now have your data in the desired format:

DATE ACTIVITY NUM

This unpivoting technique transforms your wide format into the desired long format with columns for DATE, ACTIVITY, and NUM.

Hi arimoldi ,

Yes, if you right click on the Activity column in Power Query and select 'Unpivot Other columns', this should achieve what you need.

If you 100% need to show the dates with empty values, I'd suggest populating the blanks as zeros before doing the unpivot. Not being able to see your pbix model, I don't know the context in which you are using this, but assuming a FACT table, the empty values not appearing after the unpivot shouldn't matter if you have a date dimension table your end users are filtering with in your end report.

Hope that helps!

Hello arimoldi,

Can you please try this approach:

UnpivotedData = 
VAR DatesList = {"01/10/2024", "02/10/2024", "03/10/2024", "04/10/2024", "05/10/2024"}
RETURN
UNION(
    SELECTCOLUMNS(
        FILTER('OriginalData', TRUE),
        "DATE", DATE(2024, 10, 1),
        "ACTIVITY", 'OriginalData'[ACTIVITY],
        "NUM", 'OriginalData'[01/10/2024]
    ),
    SELECTCOLUMNS(
        FILTER('OriginalData', TRUE),
        "DATE", DATE(2024, 10, 2),
        "ACTIVITY", 'OriginalData'[ACTIVITY],
        "NUM", 'OriginalData'[02/10/2024]
    ),
    SELECTCOLUMNS(
        FILTER('OriginalData', TRUE),
        "DATE", DATE(2024, 10, 3),
        "ACTIVITY", 'OriginalData'[ACTIVITY],
        "NUM", 'OriginalData'[03/10/2024]
    ),
    SELECTCOLUMNS(
        FILTER('OriginalData', TRUE),
        "DATE", DATE(2024, 10, 4),
        "ACTIVITY", 'OriginalData'[ACTIVITY],
        "NUM", 'OriginalData'[04/10/2024]
    ),
    SELECTCOLUMNS(
        FILTER('OriginalData', TRUE),
        "DATE", DATE(2024, 10, 5),
        "ACTIVITY", 'OriginalData'[ACTIVITY],
        "NUM", 'OriginalData'[05/10/2024]
    )
)

Thank you all for the answers,

I used the "Unpivot all other columns" functionality as you suggested to create the desired dataset.

I have 2 more questions:

1. if I don't insert a value (eg 0) in the column it won't be unpivoted by Power Query; is there any smart way to manage this? Or do I have to manually insert 0 in all the blank cells?

2. column "ACTIVITY" is associated to another column "MACRO-ACTIVITY" but it is a merged column, so when I use the Unpivot functionality not all the "ACTIVITY" are associated to the related "MACRO-ACTIVITY"; is there any way to do this?

Thanks,

Andrea