Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
how can i get the number of rows in all tables using Power Query M.
For Exemple:
TableName | Rows |
Table A | 50 |
Table B | 45 |
Table C | 32 |
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
Solved! Go to Solution.
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
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
This works! thank you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |