Forum Discussion

arimoldi's avatar
arimoldi
Icon for Resolver II rankResolver II
1 year ago
Solved

Transpose & Pivot using Power Query

Hi,   I have an input dataset in this format:   ACTIVITY / DATE 01/10/2024 02/10/2024 03/10/2024 04/10/2024 05/10/2024 A 8   4 1   B   3   1 5 C   3   1   D   ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi arimoldi 

     

    Excel

     

    I retested your problem by first importing the excel table into the power bi desktop and going to the power query interface to get the following table:

     

     

    1. If you don't want empty values to display data, consider replacing "null" with Spaces.

     

    In the same step, you just need to change the "0" to " ".

     

     

     

    Use the first line as the title.

     

    Delete "Changed Type1" from the step bar on the right.

     

     

    2. Select the first column and click Fill -> Down. The first column will be filled in automatically.

     

     

    Finally, the column with the date header is also selected for unpivot.

     

     

    Change the column name as required.

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

     

     

     

123abc's avatar
123abc
Icon for Community Champion rankCommunity Champion
1 year ago

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

01/10/2024A8
01/10/2024B 
01/10/2024C 
01/10/2024D 
01/10/2024E 
01/10/2024F 
02/10/2024A 
02/10/2024B3
.........

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