CODEWITHSUNDEEP
powerbidaxpowerbi-embedded
Serdia
Serdia

Reputation: 4418

How to make columns into rows from same query in Power BI Desktop

How to make columns QuotedReal and Rated as values in column ActualStatus

enter image description here

Can I pivot it somehow? Or append? Or should I create separate table and then UNION them together? To make it look like this:

enter image description here

Please help.

The final goal is to prepare data to use Horizontal Funnel by MAQ chart, which look like this:

enter image description here

UPDATE Alexis Olson thank you very much for your time and efforts. As per question 'Why' I want to use Horizontal Funnel chart, but for this visual data have to look like this:

enter image description here

So that's the whole problem. I'm massaging data in SQL and in Power BI but still didn't solve the problem. I know how to make this such data in SQL, but don't know how to achieve the same in DAX. For that I'll post another question.

Upvotes: 0

Views: 5632

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

You can certainly do this (though I'm not entirely clear as to why).

Make a copy of your Table, then Group By no column and sum over the QuotedReal and Rated columns as seen below.

Group By

That should return two columns with the sums. Then unpivot both columns and change the names of the columns to ActualStatus and CountActual. You can then append this table to your original table.

The whole process looks like this in the M code:

let
    TableSource = <Source for your table goes here>,
    #"Grouped Rows" = Table.Group(TableSource, {}, {{"QuotedReal", each List.Sum([QuotedReal]), type number}, {"Rated", each List.Sum([Rated]), type number}}),
    #"Unpivoted Columns" = Table.Unpivot(#"Grouped Rows", {"Rated", "QuotedReal"}, "ActualStatus", "CountActual"),
    #"Appended Query" = Table.Combine({TableSource, #"Unpivoted Columns"})
in
    #"Appended Query"

Upvotes: 1

Related Questions