Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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.
Solved! Go to Solution.
Hi ,
Please follow these steps to get the result set :
Dataset is :
Step 1:Select the columns HRA , Bonus , Tax and Insurance and upivot the selected 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
Thanks ,
Please mark this as the solution if you find it helpful.
Proud to be a Super User!
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:
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:
Hi ,
Please follow these steps to get the result set :
Dataset is :
Step 1:Select the columns HRA , Bonus , Tax and Insurance and upivot the selected 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
Thanks ,
Please mark this as the solution if you find it helpful.
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"
Proud to be a Super User!
Hi ,
To remove all blank rows from the table please use Remove Blank Row in Power Query Editor.
Thanks.
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 .
Could you please also share a sample dataset and what should the result set should look like
Thanks.
Proud to be a Super User!
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.
User | Count |
---|---|
93 | |
92 | |
85 | |
83 | |
49 |
User | Count |
---|---|
150 | |
142 | |
112 | |
73 | |
55 |