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.
Hello Power friends,
My question is how can I create a sample table with this sourcet:
Group, Item
1,aaaa
1,bbbb
1,cccc
1,ddddd
2,wwwww
2,rrrrrrr
2,ttttttt
2,uuuuuu
2,iiiiiii
2,dddddd
I need to create a sample table with all Group and the top 30% of the count(item) of each group, like the example bellow:
Group, Item
1,aaaa // (one item (30% ~1,2) because the group 1 has 4 records
2,wwwww // (two items (30% ~1.8) because group 2 has 6 records
2,rrrrrrr
Thanks for your time.
Solved! Go to Solution.
Hi @Anonymous,
Try this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/KLy3QUfAsSc1VitWJVjLUSQQCKDMJCKDMZCCAMlNAAMw20ikHASi7CAKgvBIIgPJKwQDKyYQAKC8Fal4sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Group", Int64.Type}, {" Item", type text}}),
//first count the number of rows then combine those with the same group into one table
#"Grouped Rows" = Table.Group(#"Changed Type2", {"Group"}, {{"Count", each Table.RowCount(_), type number}, {"Items", each _, type table [Group=number, #" Item"=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Top 30%", each
let
//get the top 30% rounded to the nearest whole number
x = Number.Round( [Count] * .30, 0)
in
//keep the first x rows only
Table.FirstN([Items],x), type table),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Items"}),
#"Expanded Top 30%" = Table.ExpandTableColumn(#"Removed Columns", "Top 30%", {" Item"}, {" Item"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Expanded Top 30%",{{" Item", type text}})
in
#"Changed Type3"
Proud to be a Super User!
Hi @Anonymous,
You can get the results by the following steps:
2. Create one calculated column Items as below screenhot
3. Create table visual, drag column [items] onto visual and filter the non-blank value of column [Items] at visual level filter
You can find all details in this sample PBIX file.
Best Regards
Rena
Hi @Anonymous,
You can get the results by the following steps:
2. Create one calculated column Items as below screenhot
3. Create table visual, drag column [items] onto visual and filter the non-blank value of column [Items] at visual level filter
You can find all details in this sample PBIX file.
Best Regards
Rena
Hi @Anonymous,
Try this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/KLy3QUfAsSc1VitWJVjLUSQQCKDMJCKDMZCCAMlNAAMw20ikHASi7CAKgvBIIgPJKwQDKyYQAKC8Fal4sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Group", Int64.Type}, {" Item", type text}}),
//first count the number of rows then combine those with the same group into one table
#"Grouped Rows" = Table.Group(#"Changed Type2", {"Group"}, {{"Count", each Table.RowCount(_), type number}, {"Items", each _, type table [Group=number, #" Item"=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Top 30%", each
let
//get the top 30% rounded to the nearest whole number
x = Number.Round( [Count] * .30, 0)
in
//keep the first x rows only
Table.FirstN([Items],x), type table),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Items"}),
#"Expanded Top 30%" = Table.ExpandTableColumn(#"Removed Columns", "Top 30%", {" Item"}, {" Item"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Expanded Top 30%",{{" Item", type text}})
in
#"Changed Type3"
Proud to be a Super User!
Here's my shot (please test on a bigger dataset)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtRJBAKlWB0QMwkIoMxkIIAyU0AAzDbSKQcBKLsIAqC8EgiA8krBAMrJhAAoLwVqXiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Group, Item" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Group, Item", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Group, Item", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Group, Item.1", "Group, Item.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Group, Item.1", Int64.Type}, {"Group, Item.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Group, Item.1", "Group"}, {"Group, Item.2", "Item"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"Group"}, {{"CountofGroup", each Table.RowCount(_), type number}, {"Details", each _, type table [Group=number, Item=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Details],"Ind",1,1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Item", "Ind"}, {"Custom.Item", "Custom.Ind"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Details"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom.Ind", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type2", "AThird", each Number.Round([CountofGroup] * 3/10)),
#"Choose rows" = Table.SelectRows(#"Added Custom1", each ( [Custom.Ind] <= [AThird])),
#"Removed Columns1" = Table.RemoveColumns(#"Choose rows",{"CountofGroup", "Custom.Ind", "AThird"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns1",{{"Custom.Item", type text}})
in
#"Changed Type3"
There are some extra steps to tidy up the data which you won't need.
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |