Forum Discussion

r_orange's avatar
r_orange
Frequent Visitor
3 years ago
Solved

remove column values with no data

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.

Need Help
  • pratyashasamal's avatar
    pratyashasamal
    3 years ago

    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's avatar
    v-jianboli-msft
    3 years ago

    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:

     

     

     

7 Replies

  • pratyashasamal's avatar
    pratyashasamal
    Memorable Member
    3 years ago

    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's avatar
      r_orange
      Frequent Visitor
      3 years ago

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

      • pratyashasamal's avatar
        pratyashasamal
        Memorable Member
        3 years ago

        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.

  • pratyashasamal's avatar
    pratyashasamal
    Memorable Member
    3 years ago

    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's avatar
      pratyashasamal
      Memorable Member
      3 years ago

      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"

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    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: