Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
r_orange
Frequent Visitor

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. 

 

r_orange_1-1670404231733.png

 

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.

2 ACCEPTED SOLUTIONS
pratyashasamal
Super User
Super User

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

This is Raw dataset which will be ingestedThis is Raw dataset which will be ingested

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

Now you will get this result :
Step 3.png

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 DatasetFinal Dataset

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

v-jianboli-msft
Community Support
Community Support

Hi @r_orange ,

 

Please try:

First, unpivot these columns:

vjianbolimsft_0-1670481101566.png

Then Pivot these columns:

vjianbolimsft_1-1670481143892.png

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:

vjianbolimsft_2-1670481162846.png

 

 

 

View solution in original post

7 REPLIES 7
v-jianboli-msft
Community Support
Community Support

Hi @r_orange ,

 

Please try:

First, unpivot these columns:

vjianbolimsft_0-1670481101566.png

Then Pivot these columns:

vjianbolimsft_1-1670481143892.png

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:

vjianbolimsft_2-1670481162846.png

 

 

 

pratyashasamal
Super User
Super User

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

This is Raw dataset which will be ingestedThis is Raw dataset which will be ingested

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

Now you will get this result :
Step 3.png

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 DatasetFinal Dataset

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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"





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





pratyashasamal
Super User
Super User

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 EditorPlease use Remove Blank Row in Power Query Editor

Thanks.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

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

pratyashasamal_0-1670408646372.png

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

Thanks.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





r_orange_5-1670410149374.png

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.