cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rygaard
Resolver I
Resolver I

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

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.

 

TableNamesLastUpdated
Items02-09-2021 10:22:29
Customers02-09-2021 10:25:10
........

 

 

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

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

3 REPLIES 3
Rygaard
Resolver I
Resolver I

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
Community Support
Community Support

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:

Icey_1-1631176303462.png

 

#sections[Section1]:

Icey_2-1631176326330.png

 

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"

 

Icey_3-1631176797381.png

Icey_4-1631176811634.png

Icey_5-1631176884299.png

Icey_6-1631176905096.png

 

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"

Icey_7-1631177229358.png

 

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"

Icey_8-1631177876986.png

 

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
Super User
Super User

@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

)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.