Sample generation chalenge

[Anonymous]

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.

[danextian · Accepted 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"

[v-yiruan-msft · Accepted solution]

Hi @Anonymous，

You can get the results by the following steps:

1. Split the column by delimiter “,” into 2 columns: Group and Item in Power Query Editor

         

      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

[Anonymous]

Thanks a lot for you time @v-yiruan-msft . Very elegant solution.

[danextian · Accepted 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"

[Anonymous]

Thanks a lot @danextian . Very good solution.

[HotChilli]

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.

[Anonymous]

Thanks a lot @HotChilli . Beatiful shot.