List of Tables and view with row count and column names in Dynamics AX 2012 R3

[excelmjr]

I am trying to understand the tables and views used by Dynamics AX 2012 R3. I have used Power Query/Power BI to connect to the server. I can list the all of the tables and views. I am trying to use RowCount to determine which tables and views are acutally being used. I then filter out the tables and views with ZERO records. When I close and load the data, I get a "Division by zero" error. So I added a new column that says "try Number.From([RowCount]) otherwise 0". I was hoping that this would change the error to a zero and then I filter out on the new column. But this does not work at either.

Has anyone else dealt with this situation? I would like to hear how you resolved it.

I am needing this information to understand how the different tables relate to each other and get a better mapping of the tables. This limits the number of tables and views, but if we start to use a new table or view, it will be added when I refresh the data.

[v-qiuyu-msft]

Hi @excelmjr,

Can you share some screenshots about this part? 

"I am trying to use RowCount to determine which tables and views are acutally being used. I then filter out the tables and views with ZERO records. When I close and load the data, I get a "Division by zero" error. So I added a new column that says "try Number.From([RowCount]) otherwise 0"."

After you retrieve data in Query Editor, which steps do you take before apply query changes? Please share detail information so we can understand the issue better. 

Best Regards,
Qiuyun Yu 

[excelmjr]

Here is the M code that I am using.

shared Views = let
Source = Sql.Databases("SERVER"),
#"Filtered Rows1" = Table.SelectRows(Source, each ([Name] = "DAX2012_Prod")),
DAX2012_Prod = #"Filtered Rows1"{[Name="DAX2012_Prod"]}[Data],
#"Filtered Rows5" = Table.SelectRows(DAX2012_Prod, each ([Kind] = "View") and ([Schema] = "dbo")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows5", "RowCount", each if Table.RowCount([Data])= null then 0 else Table.RowCount([Data])+0),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "RowCounts", each try Number.From([RowCount]) otherwise 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([RowCounts] <> 0))
in
#"Filtered Rows";