Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All
my raw date ;-
fruit,Qty
apple,123
banana,321
pinapple,213
kiwi,9
melon,5
my expected result :-
fruit,Qty
apple,123
banana,321
pinapple,213
hope some can advise .
paul
Solved! Go to Solution.
Hi @Paulyeo11
You may try my way transform your table in Power Query Editor.
If you want to keep your original table you can duplicate the table as a new one and do some transformation.
You can copy my M Query in your advanced editor. And here I set Qty <= 50 should be less sale, you can change it in Added Conditional Column Step.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lEyNDJWitWJVkpKzANCoICxkSFYoCAzD6bGyBCiJjuzPBPItQRzclNz8vOAPFOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, Qty = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Qty", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Qty] > 50 then 1 else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Custom"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Fruit]), "Fruit", "Qty"),
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Other", each [kiwi]+[melon]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Other", Int64.Type}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"kiwi", "melon"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Fruit"}, {"Value", "Qty"}})
in
#"Renamed Columns"
Result:
You can download the pbix file from this link: How to create core 3 fruit from 5 different type of fruit ?
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Paulyeo11
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
@Paulyeo11 , Create a measure like
Total Qty = sum(Table[Qty])
and then create Rank and use the visual level filter of 3 (in visual have fruit and qty)
Rank = RANKX(all(Table),[Total Qty])
or Create this measure and use. with fruit. Do not use qty
Top 3 Rank = CALCULATE([Total Qty],TOPN(3,all(Table[fruit]),[Total Qty],DESC),VALUES(Table[fruit]))
Filters: https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-report-add-filter
Hi Amit
Very sorry for not so clear in my question. On my actual application , i have 20 diff type of fruit , so those fruit that have low sales , i want to group them into Other.
Hope you can offer alternative solution.
Paul
Hi @Paulyeo11
You may try my way transform your table in Power Query Editor.
If you want to keep your original table you can duplicate the table as a new one and do some transformation.
You can copy my M Query in your advanced editor. And here I set Qty <= 50 should be less sale, you can change it in Added Conditional Column Step.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lEyNDJWitWJVkpKzANCoICxkSFYoCAzD6bGyBCiJjuzPBPItQRzclNz8vOAPFOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, Qty = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Qty", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Qty] > 50 then 1 else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Custom"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Fruit]), "Fruit", "Qty"),
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Other", each [kiwi]+[melon]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Other", Int64.Type}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"kiwi", "melon"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Fruit"}, {"Value", "Qty"}})
in
#"Renamed Columns"
Result:
You can download the pbix file from this link: How to create core 3 fruit from 5 different type of fruit ?
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |