Transpose & Pivot using Power Query

[arimoldi]

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		2		1
E			4
F				4	3

Is there any way to obtain a dataset like the following using the Power Query Editor or in any other way?

DATE	ACTIVITY	NUM
01/10/2024	A	8
01/10/2024	B
01/10/2024	C
01/10/2024	D
01/10/2024	E
01/10/2024	F
02/10/2024	A
02/10/2024	B	3
02/10/2024	C	3
02/10/2024	D	2
02/10/2024	E
02/10/2024	F
03/10/2024	A	4
03/10/2024	B
03/10/2024	C
03/10/2024	D
03/10/2024	E	4
03/10/2024	F
04/10/2024	A	1
04/10/2024	B	1
04/10/2024	C	1
04/10/2024	D	1
04/10/2024	E
04/10/2024	F	4
05/10/2024	A
05/10/2024	B	5
05/10/2024	C
05/10/2024	D
05/10/2024	E
05/10/2024	F	3

Thanks,

Andrea

[arimoldi]

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

[v-nuoc-msft]

Hi @arimoldi 

First of all, thank you very much for your prompt reply!

It seems that you already know the solution to this problem. In response to your two questions:

1. You can use "Replace Values" to replace empty values.

2. Assuming that your two tables have the same ACTIVITY column, merge the two tables based on the ACTIVITY column in the power query. And expand.

You can get a table like this.

After replacing the null value, select all date titled columns to try unpivot.

Regards,

Nono Chen

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

[arimoldi]

Hi,

thanks for the replay.

For point #1 ok, I was wondering if there was another way to manage null instead of replacing them with 0, but anyway it is fine.

For point #2 your solution seems to be applicable only in the case I have a domain table where alla ACTIVITY and MACRO_ACTIVITY are listed, but this is not the case... I have just one table and the MACRO_ACTIVITY are merged cells so when I import the dataset not all the ACTIVITY are associated to the related MACRO_ACTIVITY.

Thanks,

Andrea

[v-nuoc-msft · Accepted solution]

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]

Handling Blanks in the Unpivoted Column

Power Query can indeed leave blanks as they are during unpivoting, which can be a bit tricky if you need to replace them with zeroes.

To handle this, you can use the Replace Values function after unpivoting to automatically fill in blank cells with 0:

• In Power Query, after unpivoting, select the NUM column (or whichever column now contains the unpivoted values).
• Go to Transform > Replace Values.
• In the Replace Values dialog, leave the Value to Find field blank (this will match blank cells) and set the Replace With field to 0.
• Click OK. All blanks in this column will now be replaced with 0.

Alternatively, if you'd like to handle this step during unpivoting itself, you can first use Fill Down (if blank values indicate the same previous value) or Replace Blanks with 0 on the original table before unpivoting.

[BITomS]

@123abc @arimoldi I think there may be some confusion here - the blanks are only brought through within the unpivoting if the data type of the original date columns is text. If the data type is a number, any null values do not generate an associated row after unpivoting. I don't think the data type is specified in this thread, so it depends on this.

That said, converting the data type of the columns (if the type is currently number, which is what I assumed) to text is another option and then doing the unpivoting as per what @123abc outlined.

[BITomS]

@arimoldi , it depends if you really need the date-activity combinations with no values? You don't have to enter the zeros manually - there is another option in Power Query to 'replace values', so you could create steps to replace the blank values with a 0 for each column.

If there is another column, you can select both columns in Power Query (CTRL + Select) and then use the 'Unpivot Other Columns' functionality.

[FreemanZ]

hi @arimoldi ,

1. you can insert a 0 and replace the 0 with "" afterwards

2. try select both macro activity and activity columns, and unpivot other columns.

[arimoldi]

Hi,

thanks for the reply.

For point #1 ok, thanks.

For point #2 I tried, but since MACRO_ACTIVITY is composed by merged cells when I unpivot the tablemost of the MACRO_ACTIVITY remain null.

Thanks,

Andrea

[Sahir_Maharaj]

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]
    )
)

[BITomS]

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!

[123abc]

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/2024	A	8
01/10/2024	B
01/10/2024	C
01/10/2024	D
01/10/2024	E
01/10/2024	F
02/10/2024	A
02/10/2024	B	3
...	...	...

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

[FreemanZ]

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-f5882....