-3

I need help in creating a custom column that shows how many models per modality for each account. What would I need to input in the custom column section in power query.

enter image description here

horseyride
  • 17,007
  • 2
  • 11
  • 22
  • 2
    Do not vandalize your posts. By posting on this site, you've irrevocably granted the Stack Exchange network the right to distribute that content under the [CC BY-SA 4.0 license](//creativecommons.org/licenses/by-sa/4.0/) for as long as it sees fit to do so. For alternatives to deletion, see: [I've thought better of my question; can I delete it?](https://.com/help/what-to-do-instead-of-deleting-question) – Ethan Sep 22 '22 at 19:30

1 Answers1

3

It depends on how many other columns you have. I don't see an account column, but you mention one.

In general, in powerquery click select account and Modality columns. Right click, and use Group By. Use operation Count Rows with the new column name of your choice

enter image description here

Alternatively, [add aggregation] and use operation All Rows for that one

enter image description here

Then expand the new column using the arrows atop the new column to replace the missing data

enter image description here

edited answer to provide all potential combinations. Try

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ChildName", type text}, {"Modality", type text}, {"Model Info", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ChildName"}, {
    {"Modality per ChildName", each Table.RowCount(_), Int64.Type},
    {"Unique Modality per ChildName", each List.Count(List.Distinct(_[Modality])), Int64.Type},
     {"data", each _, type table}
}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Modality", "Model Info"}, {"Modality", "Model Info"}),
#"Grouped Rows1" = Table.Group(#"Expanded data", {"ChildName", "Modality"}, {
    {"data", each _, type table },
    {"Model Info Per Modality", each Table.RowCount(_), Int64.Type},
    {"Unique Model Info Per Modality", each List.Count(List.Distinct(_[Model Info])), Int64.Type}
}),
#"Expanded data1" = Table.ExpandTableColumn(#"Grouped Rows1", "data", {"Modality per ChildName", "Unique Modality per ChildName", "Model Info"}, {"Modality per ChildName", "Unique Modality per ChildName", "Model Info"})
in  #"Expanded data1"

enter image description here

horseyride
  • 17,007
  • 2
  • 11
  • 22
  • I have other columns which makes it not work. Would you happen to know the function I need to input in a custom column to tell power query to count specific rows based on a unique account ? the rows I need counted are in the "model info" for each modality and then total of models for each account. –  Sep 22 '22 at 18:58
  • (a) Then perhaps use an example that actually represents your data (b) do it the 2nd way , as already answered above, grouping as necessary. – horseyride Sep 22 '22 at 18:59
  • I added a pic with the 3 columns. If you can please explain how you would have it count each model per its modality then another column showing total models for each account. –  Sep 22 '22 at 19:03
  • edited answer. If this helps great, if not you need to specify better – horseyride Sep 22 '22 at 20:27