Compare 2 Tables - with result in another table

[stefan321]

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

Server	Instance	Database	Mode	CreateDate	LastFullBackup
ServerA	MSSQLSERVER	DB1	FULL	11/23/2022 13:14:00	03/22/2024 02:00:00
ServerA	MSSQLSERVER	DB2	FULL	05/23/2024 13:14:00
ServerA	MSSQLSERVER	DB3	SIMPLE	07/13/2023 13:14:00	05/22/2024 02:00:00
ServerA	Named1	DB4	FULL	09/24/2019 13:14:00	05/17/2024 13:14:00
ServerA	Named1	DB5	SIMPLE	05/22/2024 13:14:00	05/22/2024 13:14:00
ServerB	MSSQLSERVER	DB_test	FULL	07/13/2023 13:14:00	03/17/2024 13:14:00
ServerB	MSSQLSERVER	DB_1234	SIMPLE	07/21/2023 13:14:00	05/22/2024 13:14:00
ServerC	MSSQLSERVER	DB_1	SIMPLE	07/21/2023 13:14:00	05/22/2024 13:14:00

exclusion Table2

Server	Instance
ServerA	Named1
ServerC	MSSQLSERVER

result Table3

Server	Instance	Database	Mode	CreateDate	LastFullBackup
ServerA	MSSQLSERVER	DB1	FULL	11/23/2022 13:14:00	03/22/2024 02:00:00
ServerB	MSSQLSERVER	DB_test	FULL	07/13/2023 13:14:00	03/17/2024 13:14:00

[lbendlin]

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...

[stefan321]

Done.

Thanks.

[lbendlin]

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"

[stefan321]

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

[lbendlin]

 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"

[stefan321]

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.