Forum Discussion
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.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/1447523If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
- Anonymous2 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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- Ritaf1983Super User
Hi RakeshKumar21
If I understood you correctly, please refer to my solutions in the linked discussion, which includes sample files.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/1447523If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
- AnonymousNot applicable
RakeshKumar21 Drag Product in to the X-axis and again in to the Y-axis and ensure Count is selected.
Sample data: - AnonymousNot 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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.