Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
stefan321
Helper I
Helper I

Compare 2 Tables - with result in another table

Hi,

the goal is: 

Check "source Table 1" if

  • Mode= 'FULL' AND
  • CreatDate >= 8day AND
  • LastFULLBackup >= 8 days(or NULL)
  • then check result if Server and Instance combination can be found in "exclusion Table2" for exclusion.

Result should be shown in "result table3"

What is the best way to do it? Thanks a lot!

Example:

source Table1

ServerInstanceDatabaseModeCreateDateLastFullBackup
ServerA  MSSQLSERVER  DB1FULL11/23/2022 13:14:00  03/22/2024 02:00:00  
ServerAMSSQLSERVERDB2FULL05/23/2024 13:14:00 
ServerAMSSQLSERVERDB3SIMPLE  07/13/2023 13:14:0005/22/2024 02:00:00
ServerA

Named1

DB4FULL09/24/2019 13:14:0005/17/2024 13:14:00
ServerANamed1DB5SIMPLE05/22/2024 13:14:0005/22/2024 13:14:00
ServerBMSSQLSERVERDB_testFULL07/13/2023 13:14:0003/17/2024 13:14:00
ServerBMSSQLSERVERDB_1234SIMPLE07/21/2023 13:14:0005/22/2024 13:14:00
ServerCMSSQLSERVERDB_1SIMPLE07/21/2023 13:14:0005/22/2024 13:14:00

 

exclusion Table2

ServerInstance
ServerA  Named1  
ServerCMSSQLSERVER

 

result Table3

ServerInstanceDatabaseModeCreateDateLastFullBackup
ServerA  MSSQLSERVER  DB1FULL11/23/2022 13:14:00  03/22/2024 02:00:00  
ServerB  MSSQLSERVERDB_testFULL07/13/2023 13:14:0003/17/2024 13:14:00

 

6 REPLIES 6
lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Done.

Thanks.

Why would ServerB be eligible?  CreatedDate is not yesterday or today.  Are these timestamps in UTC?

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndBBC4IwFMDxryKehe29TURvWQbBjHLURSSCduxi0ufvOcN0LIMukz3cj/9W16E27dO0qyAIo7DU+qh0UZ2Lyu43OdC6PSlFHwCGgiFHDEBkIDPO7U+chtjPZcCRhsO8iUZ6DlsWPyyP36wcWZr+AgStelceVDE0JAwsIqZITztlDru/3s0NrCgnSSlDSecgdTRInNCvWjz2zTv8dR4v91z60plHN8n0X1osZXpZQCFnvXQell7TA6+98J9q8wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Server = _t, Instance = _t, Database = _t, Mode = _t, CreateDate = _t, LastFullBackup = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CreateDate", type datetime}, {"LastFullBackup", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Mode] = "FULL")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [CreateDate] > DateTime.From(Date.AddDays(Date.From(DateTimeZone.UtcNow()),-1))),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each [LastFullBackup] is null or [LastFullBackup] >  DateTime.From(Date.AddDays(Date.From(DateTimeZone.UtcNow()),-8))),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows2", {"Server", "Instance"}, exclusion, {"Server", "Instance"}, "exclusion", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "rows", each Table.RowCount([exclusion])),
    #"Filtered Rows3" = Table.SelectRows(#"Added Custom", each [rows] = 0)
in
    #"Filtered Rows3"

You mean why is ServerB reflecting in "result Table3", right?

CreatedDate from ServerB Databbase "DB_test" is 07/13/2023 13:14:00 and therefore older than 1 Day (CreatDate >= 1day). Same for LastFULLBackup.

Additionally ServerB is not in "exclusion Table2". 

 

 

 therefore older than 1 Day (CreatDate >= 1day).

 

 

So you mean    CreatedDate is less than yesterday?

 

Please restate your logic to reduce ambiguity.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndBBC4IwFMDxryKehe29TURvWQbBjHLURSSCduxi0ufvOcN0LIMukz3cj/9W16E27dO0qyAIo7DU+qh0UZ2Lyu43OdC6PSlFHwCGgiFHDEBkIDPO7U+chtjPZcCRhsO8iUZ6DlsWPyyP36wcWZr+AgStelceVDE0JAwsIqZITztlDru/3s0NrCgnSSlDSecgdTRInNCvWjz2zTv8dR4v91z60plHN8n0X1osZXpZQCFnvXQell7TA6+98J9q8wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Server = _t, Instance = _t, Database = _t, Mode = _t, CreateDate = _t, LastFullBackup = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CreateDate", type datetime}, {"LastFullBackup", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Mode] = "FULL")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [CreateDate] < DateTime.From(Date.AddDays(Date.From(DateTimeZone.UtcNow()),-1))),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each [LastFullBackup] is null or [LastFullBackup] <  DateTime.From(Date.AddDays(Date.From(DateTimeZone.UtcNow()),-8))),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows2", {"Server", "Instance"}, exclusion, {"Server", "Instance"}, "exclusion", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "rows", each Table.RowCount([exclusion])),
    #"Filtered Rows3" = Table.SelectRows(#"Added Custom", each [rows] = 0)
in
    #"Filtered Rows3"

Yes correct.

I have increased the CreatedDate to >= 8 days but anything else is the correct logic in my point of view.

In simple words: i want to check if a Database Backup is too old.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.