Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
the goal is:
Check "source Table 1" if
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 |
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.
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
10 | |
6 |