Forum Discussion

RakeshKumar21's avatar
RakeshKumar21
Regular Visitor
2 years ago
Solved

get the word count

Hi All

I have the record which contains or data "Apple, Banana, Apple, Carrot, Banana, etc". Now i would like to know how many apples, banana are there in the record, so that i can show that in PI cart... 

Can someone help me on this.

  • Hi RakeshKumar21 
    If I understood you correctly, please refer to my solutions in the linked discussion, which includes sample files.

    https://community.fabric.microsoft.com/t5/Desktop/Pie-Chart-Separating-Values-in-Same-Row/m-p/3245643

    If not 

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
    Please show the expected outcome based on the sample data you provided.

    https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi RakeshKumar21 ,

     

    According to your statement, I think your data should be Records.

    I suggest you to try Record.ToList() function to change Record to List.

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIsKMhJBdJOiXlAiCTgnFhUlF+CkInViVZyAnLDE0tSi3JTc/LzsOmG6/IvSsxLTwXrcsZiGkIrjFaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Fruit1 = _t, Fruit2 = _t, Fruit3 = _t, Fruit4 = _t, Fruit5 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Fruit1", type text}, {"Fruit2", type text}, {"Fruit3", type text}, {"Fruit4", type text}, {"Fruit5", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Records", each _),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Category", "Fruit1", "Fruit2", "Fruit3", "Fruit4", "Fruit5"}),
        #"Added Custom1" = Table.AddColumn(#"Removed Columns", "RecordToList", each Record.ToList([Records])),
        #"Expanded RecordToList" = Table.ExpandListColumn(#"Added Custom1", "RecordToList"),
        #"Grouped Rows" = Table.Group(#"Expanded RecordToList", {"RecordToList"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
        #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([RecordToList] <> "" and [RecordToList] <> "A" and [RecordToList] <> "B" and [RecordToList] <> "C"))
    in
        #"Filtered Rows"

    Result:

    Then create a line chart.

     

    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.

     

     

3 Replies

  • Hi RakeshKumar21 
    If I understood you correctly, please refer to my solutions in the linked discussion, which includes sample files.

    https://community.fabric.microsoft.com/t5/Desktop/Pie-Chart-Separating-Values-in-Same-Row/m-p/3245643

    If not 

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
    Please show the expected outcome based on the sample data you provided.

    https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

     

  • Anonymous's avatar
    Anonymous
    Not applicable

    RakeshKumar21  Drag Product in to the X-axis and again in to the Y-axis and ensure Count is selected.

    Sample data:

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi RakeshKumar21 ,

     

    According to your statement, I think your data should be Records.

    I suggest you to try Record.ToList() function to change Record to List.

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIsKMhJBdJOiXlAiCTgnFhUlF+CkInViVZyAnLDE0tSi3JTc/LzsOmG6/IvSsxLTwXrcsZiGkIrjFaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Fruit1 = _t, Fruit2 = _t, Fruit3 = _t, Fruit4 = _t, Fruit5 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Fruit1", type text}, {"Fruit2", type text}, {"Fruit3", type text}, {"Fruit4", type text}, {"Fruit5", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Records", each _),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Category", "Fruit1", "Fruit2", "Fruit3", "Fruit4", "Fruit5"}),
        #"Added Custom1" = Table.AddColumn(#"Removed Columns", "RecordToList", each Record.ToList([Records])),
        #"Expanded RecordToList" = Table.ExpandListColumn(#"Added Custom1", "RecordToList"),
        #"Grouped Rows" = Table.Group(#"Expanded RecordToList", {"RecordToList"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
        #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([RecordToList] <> "" and [RecordToList] <> "A" and [RecordToList] <> "B" and [RecordToList] <> "C"))
    in
        #"Filtered Rows"

    Result:

    Then create a line chart.

     

    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.