Latest Enterprise DNA Initiatives

Pivot a table with multiple columns

Hi,
I have a dax calculated table which I have copied and added as a manual pasted table from “Enter data” that look like this:

image

but i would like to pivot it with power query so it looks like this

I have a feeling that I have seen this topic on the EDNA youtube channel where melissa does something similar but for some reason i cant find it now.

I also attach my sample PBIX if someone could help me out :slight_smile:

Pivot multiple columns.pbix (43.9 KB)

1 Like

Hi @Tibbie

Pivot multiple columns.pbix (61.1 KB)

I believe this is what you are looking for.

In power query when you right click on a column you can select “Unpivot Other Colum” to get the desired result. I have done it for your reference.

Hope this answer your question.

Regards,
Hemant

@Hemantsingh sorry but i forgot that i need two value columns (min and max) like this so it shows the span

Hi @Tibbie,

See if this meets your requirement. I’ve referenced your Query so you can paste this into a new blank query in the same file.

let
    Source = UnitPriceSegments,
    HelperStep = Table.AddColumn(Source, "Temp", each #table( {"Quartile", "Min", "Max"}, {{ List.Skip(Table.ColumnNames(Source), 1), List.Skip(Record.ToList(_),1), List.Skip(Record.ToList(_), 1) }}))[[CategoryID], [Temp]],
    ExpandTemp = Table.ExpandTableColumn(HelperStep, "Temp", {"Quartile", "Min", "Max"}, {"Quartile", "Min", "Max"}),
    ExpandQuartile = Table.ExpandListColumn(ExpandTemp, "Quartile"),
    GetMin = Table.ReplaceValue(ExpandQuartile, each [Min], each [Min]{Number.From( Text.Start([Quartile], 1))-2}, Replacer.ReplaceValue,{"Min"}),
    GetMax = Table.ReplaceValue(GetMin, each [Max], each [Max]{Number.From( Text.Start([Quartile], 1))-1}, Replacer.ReplaceValue,{"Max"}),
    ChangeType = Table.TransformColumnTypes(GetMax,{{"Quartile", type text}, {"Min", type number}, {"Max", type number}}),
    ReplaceErrors = Table.ReplaceErrorValues(ChangeType, {{"Min", 0}})
in
    ReplaceErrors

With this result.

I hope this is helpful.

1 Like

You are a true wizard Melissa :slight_smile:
Will review the steps and learn from it. Thanks a lot!

2 Likes