How do I keep my dates when I transpose a query?

[barbarabraga]

Hello everyone,

I have a query that is connected to a Smartsheet file and I am having troubles while transposing my table. Originally, my KPIs are in rows (metrics below) and my month/year are column headers and I wish exactly the opposite: KPIs in columns and month/year in rows (see below).

When I transpose the table, the dates simply disappear! (see below)

How can I transpose witouth loosing the dates?

Thank you,

Barbara Braga

[AkhilAshok · Accepted solution]

You can try the following steps:

1. Click on the Matric column and select Transform -> Unpivot Other columns

2. Click on Matric column again and select Transform -> Pivot Column, selecting Values column as Value

[barbarabraga]

Hi! Thanks...it almost worked.

Now my dates are in a single column and my KPIs are headers. However, the KPIs number appears now as 0 or 1, and these are not real number. How can I get the real numbers?

[AkhilAshok]

Can you check if the last part of the M-code in the formula bar in List.Sum?

= Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Metric]), "Metric", "Value", List.Sum)

[barbarabraga]

I think it worked. While doing the second step (2. Click on Matric column again and select Transform -> Pivot Column, selecting Values column as Value) I went to advanced options and in Aggregate Value Function I selected "Don't aggregate"option.

The last M-code in the formula is: = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Metric]), "Metric", "Value")

It worked perfectly for the majority of the KPI (the actual number came out). However, for some of them the error message was displayed instead of the actual number that is inside the Smartsheet. Any clue why? Is this probably an error on the Smartsheet-PBI communication?

[AkhilAshok]

Can you check what the error says?

[barbarabraga]

[AkhilAshok]

I think the error is happening probably because, you gave don't aggregate while pivoting. You could try "Sum" or "Max" instead of "Don't aggregate"