Forum Discussion

mrfazio's avatar
mrfazio
New Member
6 years ago

Power query - Counting rows from all table in query editor but not shows data in the model tab

I want to consolidate counting rows from all my tables that I import from excel in power bi.

I did that using a script in power query:

 

let
Source = #shared,
#"Converted to Table" = Record.ToTable(Source),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each Type.Is(Value.Type([Value]),Table.Type) and [Name] <> "Query1"),
#"Consolidate Table" = Table.AddColumn(#"Filtered Rows", "Column", each Table.RowCount([Value]) )
in
#"Consolidate Table"

 

The data can be showed in query editor but when I changed to the model tab or building the report the power bi dont show any data.

 

Can anyone help me?

Need Help

5 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, mrfazio 

     

    Based on your description, I'd like to suggest you replace the parameter for 'Table.rowcount' with #'Filtered Rows'. The function needs a table as a parameter.

    Table.RowCount(table as table) as number

     

    When you load the model in Power BI Desktop, the column with data which is table data type won't be loaded into the model. I'd like to suggest you extend the column to display the corresponding data before you click 'Close and Apply'. The pbix file is attached in the end.

     

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • mrfazio's avatar
      mrfazio
      New Member
      6 years ago

      Hi, thanks for trying to help me.

       

      Im going to explain every step that I did:

       

      I create two excel files and Import the files in power bi:

       

      So, I create a empty query and edit the query :

       

      let
      Source = #shared,
      #"Converted to Table" = Record.ToTable(Source),
      #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each Type.Is(Value.Type([Value]),Table.Type) and [Name] <> "Consulta1"),
      #"Consolidate Table" = Table.AddColumn(#"Filtered Rows", "Column", each Table.RowCount([Value]) ),
      #"Value Expandido" = Table.ExpandTableColumn(#"Consolidate Table", "Value", {"Field1", "Field2"}, {"Value.Field1", "Value.Field2"})
      in
      #"Value Expandido"

       

      The first line:

      Source = #shared,

       

      Take all variables used by power bi, tables imported, function, etc.

       

      The next step I convert to a table

       

      #"Converted to Table" = Record.ToTable(Source),

       

      Then, I filter only the variables wich is the type of table. I need to remove the table related to the query I made, in my case the query has the name "Consulta1".

      #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each Type.Is(Value.Type([Value]),Table.Type) and [Name] <> "Consulta1"),

       

      I  get the tables and calculate the row count

      #"Consolidate Table" = Table.AddColumn(#"Filtered Rows", "Column", each Table.RowCount([Value]) ),

       

      Then I tried your suggest, expanding the column, but not worked for me.

      #"Value Expandido" = Table.ExpandTableColumn(#"Consolidate Table", "Value", {"Field1", "Field2"}, {"Value.Field1", "Value.Field2"})

       

      But when I switched to model, any data appear:

       

       

       
      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        6 years ago

        Hi, mrfazio 

         

        Is there any error message with these steps? What is the result for the query? 

         

        Best Regards

        Allan