Forum Discussion
Help: Reordering the Values of each Row in Numerical Order
Raw Data SampleDesired Result
I have a company hierarchy levels that are in one column but they are not in numerical orders(image 1).
My end goal is to have the levels in numerical order in one column(image 2).
Could someone help me accomplish this in Power Query? I've included screenshots above showing what I'm trying to accomplish.
So far I've tried the following steps:
split the column by the comma into 7 different columns
extracting the second character from each of the 7 split column to determine the level number.
Then tried to merge all 7 columns back into one column using conditional columns based on the level number extracted in the previous step. In this step I get null in all the rows once I combined the fifth column with the previous 4 columns.
I feel like I'm over complicating this and maybe there is an easier way to accomplish with M code.
If anyone could help, I'd highly appreciated.
Right click on the column --> Transform --> click on anything like lower case then edit the formula
= Table.TransformColumns(#"Changed Type",{{"Column1", each Text.Combine(List.Sort(Text.Split(_, ", ")), ", "), type text}})
