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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
89 | |
84 | |
76 | |
64 |
User | Count |
---|---|
135 | |
112 | |
98 | |
98 | |
92 |