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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors