Forum Discussion

godey4me's avatar
godey4me
Regular Visitor
3 years ago
Solved

Counting rows and using text as column headers

I have a table in this form:

OrgCase
AFA
BSI
ASI
CSI
BSI
CFA
AFA

I'd like to have the target table results such that all the occurrence of FA or SI are counted like so:

 

ORGFASI
A21
B20
C11

 

How can I achieve this? Thanks

Need Help
  • latimeria's avatar
    latimeria
    3 years ago

    Hello godey4me ,

     

    Try this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KV9JRck4sTlWK1YlWcgRy3BzBTCcgM9gTLgplOiOYTqiiUG0wE2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Grouped Rows" = Table.Group(
        #"Promoted Headers", 
        {"Org", "Case"}, 
        {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(
        #"Grouped Rows", 
        List.Distinct(#"Grouped Rows"[Case]), "Case", "Count", List.Sum)
in
    #"Pivoted Column"

     

     

     

1 Reply

  • latimeria's avatar
    latimeria
    Solution Specialist
    3 years ago

    Hello godey4me ,

     

    Try this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KV9JRck4sTlWK1YlWcgRy3BzBTCcgM9gTLgplOiOYTqiiUG0wE2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Grouped Rows" = Table.Group(
        #"Promoted Headers", 
        {"Org", "Case"}, 
        {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(
        #"Grouped Rows", 
        List.Distinct(#"Grouped Rows"[Case]), "Case", "Count", List.Sum)
in
    #"Pivoted Column"