Sample generation chalenge

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"

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