Show max value from "timestamp" from ALL tabels in matrix

[Rygaard]

I have 50 tables, all of them have a timestamp (pr line)

I would like a matrix that show  the table name and the max timestamp pr line.

Now i could make a table where i cod this pr table, but i want it to be dynamic, so when i add a new table it auto ads it.

The purpose is ofc to check if the source is as updated as expected.

TableNames	LastUpdated
Items	02-09-2021 10:22:29
Customers	02-09-2021 10:25:10
....	....

Is this even posible ? (or should I SQL this new table on the DataWarehouse)

[Rygaard]

Yahh.. but I was kinda hoping to automate this so I did not have to add a new line for each table... since I will most surly forget this when i get a new table 😛

[Icey]

Hi @Rygaard ,

It is not supported to use DAX to get all table names and aggregated values dynamically. Please try the method @amitchandak mentioned.

Usually, we can use #shared or #sections[Section1] to return all queries in Power Query. 

#shared:

#sections[Section1]:

And we can filter the table to only keep tables we needed. The entire M codes are as follows:

#shared:

let
    Source = #shared,
    #"Converted to Table" = Record.ToTable(Source),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Converted to Table", {"Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Errors", each [Value] is table),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Name] <> "Query1")),
    #"Aggregated Value" = Table.AggregateTableColumn(#"Filtered Rows1", "Value", {{"timestamp", List.Max, "Max of timestamp"}})
in
    #"Aggregated Value"

#sections[Section1]:

let
    Source = #sections[Section1],
    #"Converted to Table" = Record.ToTable(Source),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Converted to Table", {"Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Errors", each [Value] is table),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Name] <> "Query1")),
    #"Aggregated Value" = Table.AggregateTableColumn(#"Filtered Rows1", "Value", {{"timestamp", List.Max, "Max of timestamp"}})
in
    #"Aggregated Value"

However, this method only works inside the Power Query Editor. The environments of #shared and #sessions are not available when you load data into Power BI.

Therefore, it can't meet your requirements.

Let consider other methods.

If your data source is SQL Server, you may try this:

let
    Source = Sql.Database("yourserver", "yourdatabase"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Data"}),
    #"Aggregated Data" = Table.AggregateTableColumn(#"Removed Other Columns", "Data", {{"timestamp", List.Max, "Max of timestamp"}})
in
    #"Aggregated Data"

You need to import new tables manually and it will return all tables in your SQL Server database. 

If your data source is Excel, it is similar with SQL.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Admin\Desktop\Test.xlsx"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Table")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"timestamp"}, {"timestamp"}),
    #"Grouped Rows" = Table.Group(#"Expanded Data", {"Name"}, {{"Max of timestamp", each List.Max([timestamp]), type datetime}})
in
    #"Grouped Rows"

Please let me know if you have any confusion.

Best Regards,

Icey

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

[amitchandak · Accepted solution]

@Rygaard , One way is to create a new DAX table

union(

summarize(Table, "Name", "Table1", "Date", max(Table1[Date])),

summarize(Table, "Name", "Table2", "Date", max(Table2[Date])),

summarize(Table, "Name", "Table3", "Date", max(Table3[Date]))

// add others

)