Forum Discussion
Clean data in Power query editor
I have data that I am trying to clean in the Power query editor
The output should be like this-
I am facing issues while filtering the null from value, column 3 and column 6
Hi amikm ,
There is a question why the numbers in the sample data are empty, and the results will be 2 and 2863?
At present, according to my own understanding, I made the expected results as follows, if in doubt, please let me know.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Plese post a dummy file without confidential data to enable volunteers here to work on this problem. You can host the file on Onedrive / other file hosts and share the link here.
Edit - Also, as per logic, Pay Direct Drugs should also appear in your output.
Vijay_A_Verma, I don't want to to use any drug-related thing as Category, so we are going to exclude
I would first add a column with just the Experience Groups and nulls for the rows that are not Experiance Group:
= Table.AddColumn(PriorStepOrTableName, "Experience Group", each if Text.Contains([Value], ":") then [Value] else null, type text)
Then Fill Down on your new Experience Group Column, and then Fill Down on the Value Column.
Now group by Experience Group and Value, and use the Sum Aggregation on Column3 and Column6.
That should do it!
--Nate
Hi amikm ,
There is a question why the numbers in the sample data are empty, and the results will be 2 and 2863?
At present, according to my own understanding, I made the expected results as follows, if in doubt, please let me know.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
