remove column values with no data

Hi ,
Please follow these steps to get the result set :
Dataset is :

This is Raw dataset which will be ingested

Step 1:Select the columns HRA , Bonus , Tax and Insurance and upivot the selected columns
Upivot the columns

Now you will get this result :

Step 2: Now select the columns Attribute and Value .
Now pivot these to columns on basis of "Value" column .
Now you will get the final dataset
Final Dataset

Thanks ,
Please mark this as the solution if you find it helpful.

Hi r_orange ,

Please try:

First, unpivot these columns:

Then Pivot these columns:

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMTS1W0lEyNTAwAFJGBhDaFExCUawOTnVAZGZqQJQ6IDKEcggqBCJjIBekziczpxLItYS7C7v7sCvDcB5OZWiuw6cOyXEeiUVFIHXmEHljUxyuw6EOw3m41aG5D69CmANjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t, #"BASIC SALARY" = _t, #"GROSS SALARY" = _t, HRA = _t, TAX = _t, BONUS = _t, INSURANCE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME", type text}, {"BASIC SALARY", Int64.Type}, {"GROSS SALARY", Int64.Type}, {"HRA", Int64.Type}, {"TAX", Int64.Type}, {"BONUS", Int64.Type}, {"INSURANCE", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"NAME", "BASIC SALARY", "GROSS SALARY"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Final output: