Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Number of Rows of All Tables in Power Query

how can i get the number of rows in all tables using Power Query M.

 

For Exemple:

 

TableNameRows
Table A50
Table B45
Table C32

 

I tried this, but it doesn't work:

 

 

 

 

let
        Fonte = #sections[Section1]
    
    ,   ToTable = Record.ToTable(Fonte)
    
    ,   ExcludeDQ = Table.SelectRows(ToTable, each (
                                                    [Name] <> "Qualidade"
                                                    )
                                            )

    ,   ListofTables = ExcludeDQ[Name]

    ,   AddColumn = Table.AddColumn(
                                        ExcludeDQ
                                        ,"Quantidade de Linhas"
                                        ,each ListofTables Table.RowCount(ListofTables)
    )
    )
  
in
        ExcludeDQ

 

 

 

 

 

Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous 

     

    Using your approach - what you need to pass to RowCount is [Value] rather than [Name]:

    let
    Source = #sections[Section1],
    #"Converted to Table" = Record.ToTable(Source),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "RowCount", each Table.RowCount([Value]))
in
    #"Added Custom"

     

    However, this won't take you anywhere past the Editor. It does not work correctly in the Visuals Editor and not going to refresh - actually will halt on error - online. So, if the purpose of this is purely design-time statistics - Ok, if you want to use it anywhere past the PQ Editor - use the approach suggested by Jimmy. Do not forget that you need not Table Names, it is actually ReferencetoTable (i.e. Table vs. "Table" in the list).

     

    Kind regards,

    JB

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 

     

    Using your approach - what you need to pass to RowCount is [Value] rather than [Name]:

    let
    Source = #sections[Section1],
    #"Converted to Table" = Record.ToTable(Source),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "RowCount", each Table.RowCount([Value]))
in
    #"Added Custom"

     

    However, this won't take you anywhere past the Editor. It does not work correctly in the Visuals Editor and not going to refresh - actually will halt on error - online. So, if the purpose of this is purely design-time statistics - Ok, if you want to use it anywhere past the PQ Editor - use the approach suggested by Jimmy. Do not forget that you need not Table Names, it is actually ReferencetoTable (i.e. Table vs. "Table" in the list).

     

    Kind regards,

    JB

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This works! thank you

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    6 years ago
    Hello

    List.Count(Table.ColumnNames(yourtable)) should do the trick. However you need a table or a list where all your tables are listed.

    Jimmy