remove column values with no data

[r_orange]

I have put this columns in a table, as you can see it has multiple records with same fields due to the columns present in red rectangle.  actually I have pivoted those columns from a single column hence they have only one value throught whole column. 

QUES: i want to only see 1 record, which will ONLY have values that has data and are NOT empty. I have tried many joining conditions, many times changed query but didnt get the result. Would really appreciate any help.

[pratyashasamal · Accepted solution]

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.

[v-jianboli-msft · Accepted solution]

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:

[pratyashasamal · Accepted solution]

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.

[pratyashasamal]

Here is the M Code if it helps you  :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMTS1W0lEyNTAwAFJGBhDaFExCUawOTnVAZGZqQJQ6IDKEcggqBCJjIDc2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t, #"Basic Salary" = _t, #"Gross Salary" = _t, HRA = _t, TAX = _t, BONUS = _t, INSURANCE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Names", 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", {"Names", "Basic Salary", "Gross Salary"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
#"Pivoted Column"

[pratyashasamal]

Hi ,
To remove all blank rows from the table please use Remove Blank Row in Power Query Editor.
Please use Remove Blank Row in Power Query Editor

Thanks.

[r_orange]

thanks for replying, but I actually want to remove blank column values, and not whole row. makes sense?

[pratyashasamal]

If you want to just remove the blank column you could use Choose Column and just select the columns that you need .

Could you please also share a sample dataset and what should the result set should look like 

Thanks.

[r_orange]

as you can see, I have pivoted the table in yello highlight and used in current output, but i want to remove the empty columns and get the desired output like I have shown in image.