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"

     

     

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