cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
excelmjr Regular Visitor
Regular Visitor

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

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.

 

 

2 REPLIES 2
Moderator v-qiuyu-msft
Moderator

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

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
excelmjr Regular Visitor
Regular Visitor

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

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";

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)