Forum Discussion

Caluminium's avatar
Caluminium
Regular Visitor
6 years ago
Solved

Summarizing text as a number

Hi everyone,   I have some data that includes the name of schools and the name of teachers that teach at those schools. I'm trying to summarise this data so that it shows me how many schools have 3...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Caluminium ,

     

    You could count data in Query Editor like this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc/BDoIwDAbgV1l25inEg0EXTTwSDnUUmZSNbCNkb+8CJ4j1/PVv/9a1LL3BrhOTNyP4JAupvPi4pFE2xW89z7aFwPIJjG85DaJyFrlwEFdE2tCNmQ+HM2LiOORielhVzbE3tN+s3AIxrvxAH/tDaQI9ECyBG1CaECynJYzTC4k4vy+4ZZ/ReDL2ve92c/88f3ZJa/fmCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"School name" = _t, #"Teacher name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"School name", type text}, {"Teacher name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"School name"}, {{"Count School", each Table.RowCount(Table.Distinct(_)), Int64.Type}, {"Count Teacher", each Table.RowCount(Table.Distinct(_)), Int64.Type}, {"ALL", each _, type table [School name=text, Teacher name=text]}}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Teacher name"}, {"ALL.Teacher name"})
in
    #"Expanded ALL"

     

     

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

Caluminium when you dropping the teacher name on the visual, next to the column drop-down arrow, change the aggregation to don't summarize

 

 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

Caluminium's avatar
Caluminium
Regular Visitor
6 years ago

Hi parry2k ,

That works really well when I have the teacher data as number in the first example, but when the names are stored as text it's just showing each name with a school count of 1 rather than summarising them into the different quantity groups:

 

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Caluminium ,

     

    You could count data in Query Editor like this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc/BDoIwDAbgV1l25inEg0EXTTwSDnUUmZSNbCNkb+8CJ4j1/PVv/9a1LL3BrhOTNyP4JAupvPi4pFE2xW89z7aFwPIJjG85DaJyFrlwEFdE2tCNmQ+HM2LiOORielhVzbE3tN+s3AIxrvxAH/tDaQI9ECyBG1CaECynJYzTC4k4vy+4ZZ/ReDL2ve92c/88f3ZJa/fmCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"School name" = _t, #"Teacher name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"School name", type text}, {"Teacher name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"School name"}, {{"Count School", each Table.RowCount(Table.Distinct(_)), Int64.Type}, {"Count Teacher", each Table.RowCount(Table.Distinct(_)), Int64.Type}, {"ALL", each _, type table [School name=text, Teacher name=text]}}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Teacher name"}, {"ALL.Teacher name"})
in
    #"Expanded ALL"

     

     

    • Caluminium's avatar
      Caluminium
      Regular Visitor
      6 years ago

      Amazing - that worked perfectly! Thanks

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Caluminium , you need to have some kind of grouping to get the data grouped like school name, or any other group by. Else you will only get one row.