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:
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 
Pivot multiple columns.pbix (43.9 KB)
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.
You are a true wizard Melissa
Will review the steps and learn from it. Thanks a lot!
Hey Tibbie - there is a great video from curbal that explains pivoting and unpivoting in Power Query really well which is a great watch for reference:
