Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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}})
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}})
A big THANK YOU! This did the trick.