Clean data in Power query editor

[amikm]

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

[Anonymous · Accepted solution]

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.

[Anonymous]

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

[Vijay_A_Verma]

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. 

[amikm]

@Vijay_A_Verma, I don't want to to use any drug-related thing as Category, so we are going to exclude