Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All, I have a list of devices with different software installation dates and versions.
It was originally multiple rows per version for the same device so I grouped it in Power Query.
I'm trying to return the, if available - The last 3 dates descending entries in the comma separated list for the BIOSVersions, BIOSDates, and BIOSStatuses. N, N-1, N-2. So maybe 6-9 Measures? or what would be the best approach. I am also trying to perform a datediff on the potentially three date values.
Essentially, I am trying to get an output like this:
(On further review, I think my grouping had an issue).
This is my sample data.
Device Serial | Device Name | Latest Bios Version | BIOSVersions | BIOSDates | BIOSStatuses |
1 | a | 01.24.00 | 04.05, Q70 Ver. 01.21.02, Q70 Ver. 01.04.05, Q70 Ver. 01.22.00, Q70 Ver. 01.07.00, Q70 Ver. 01.23.00, Q70 Ver. 01.15.00, Q70 Ver. 01.17.00 | 2022-08-01, 2018-11-16, 2022-09-15, 2019-04-15, 2022-12-09, 2020-12-28, 2021-08-05 | Old, Old, Old, Old, Old, Old, Old, Old |
2 | b | 02.12.00 | 00.30.00, 00.33.01, 00.41.00, 00.43.00, 02.11.01 | 2022-12-07, 2023-01-06, 2023-01-18, 2023-02-08, 2023-03-29 | Old, Old, Old, Old, Old |
3 | c | 0.00.26 | 10/9/2020 | Unknown | |
4 | d | 01.13.01 | T95 Ver. 01.11.00, T95 Ver. 01.12.00 | 2022-10-17, 2023-01-03 | Old, Old |
5 | e | 80.3 | 00.18, 80.28 | 2014-07-25, 2018-11-06 | Old, Old |
Thank you in advance for any and all help.
Solved! Go to Solution.
Hi,
This M code works
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZTNbsMgDMdfpcq5INuQAE8x9bBdqh62tadNmbRLX38mdreoqgKeFARy/JPh74/jccBhP7zyAvQUPQAfeRt54+/p8zyc9g+dDgl3L5dvv2MbKIGTQ3QEmA1sEhuODmJli4EtHohtVDSugUUQGwQHlSWwsUvc6ICsLF87aNzJyrKtuhDrDFZ2FBtlh+Y7Z40bRWdCA1s0v1F1pn6WNEdEjuUysig54qCQrWzQ2iiq1Zqt57dWwzxwOiToa5gWu9UwDXazCFqsxoVRBcV+djMZLZb+3lus7FYiaxe+15/kUWMA+HCbC6kfCdIe3M9S4qGJRNRH5X7k9pasQ6eF0DJrUOaj9M8aiXw+S9XWJ3uaVKjfyfI8f8xf1/kf3rW2L7wyi7MQS4ETZzDVio2ra9y58kZZtLzrjNMP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Device Serial" = _t, #"Device Name" = _t, #"Latest Bios Version" = _t, biosmfgver = _t, biosdate = _t, biosstatus = _t]),
#"Filtered Rows1" = Table.SelectRows(Source, each ([biosstatus] = "Old")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"biosstatus"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Device Serial", type text}, {"Device Name", type text}, {"Latest Bios Version", type text}, {"biosmfgver", type text}, {"biosdate", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Device Serial"}, {{"All", each _, type table [Device Serial=nullable text, Device Name=nullable text, Latest Bios Version=nullable text, biosmfgver=nullable text, biosdate=nullable date, biosstatus=nullable text]}}),
biosdaterank = Table.TransformColumns(#"Grouped Rows",{"All", each Table.AddRankColumn(_,"Daterank",
{"biosdate", Order.Descending},
[RankKind = RankKind.Competition]
)}),
#"Removed Columns" = Table.RemoveColumns(biosdaterank,{"Device Serial"}),
#"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"Device Serial", "Device Name", "Latest Bios Version", "biosmfgver", "biosdate", "Daterank"}, {"Device Serial", "Device Name", "Latest Bios Version", "biosmfgver", "biosdate", "Daterank"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded All", each [Daterank] <= 3),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Device Serial", "Device Name", "Latest Bios Version", "Daterank"}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","bios","",Replacer.ReplaceText,{"Attribute"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","mfg","",Replacer.ReplaceText,{"Attribute"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value1", {{"Daterank", type text}}, "en-IN"),{"Attribute", "Daterank"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value"),
Datecolumns = Table.FromList(List.Select(Table.ColumnNames(#"Pivoted Column"), each Text.Contains(_,"date",Comparer.OrdinalIgnoreCase)), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(Datecolumns, "Custom", each Value.Type(#date(2023,1,1))),
datatypelist = List.Zip({#"Added Custom"[Column1],#"Added Custom"[Custom]}),
Result = Table.TransformColumnTypes(#"Pivoted Column",datatypelist),
#"Datediff 1" = Table.AddColumn(Result, "Date Diff 1", each Duration.Days([date 1] - [date 2]), Int64.Type),
#"Datediff 2" = Table.AddColumn(#"Datediff 1", "Date Diff 2", each Duration.Days([date 2] - [date 3]), Int64.Type)
in
#"Datediff 2"
Hope this helps.
Is this what you are looking for?
you can get last 3 records in power query like this
List.LastN(
Text.Split([BIOSDates],","),3)
Hi,
I am not clear about he expected result. Share the raw dataset and populate the table of expected results.
Hi, Apologies, I couldn't find a file upload button.
https://we.tl/t-micPll22kt This is the file I can provide after grouping
https://we.tl/t-Tdl2Oh4PgD raw file.
I am trying to get the most recent 3 dates for a device, and the most 3 recent versions of the BIOS.
I think my grouping put them out of order somehow, sorry. This is the desired output mockup; Thank you.
Device Serial | Device Name | Latest Bios Version | Date 1 | Version 1 | Date 2 | Version 2 | Date 3 | Version 3 | Date Diff 1 | Date Diff 2 |
1 | a | 01.24.00 | 12/9/2022 | Q70 Ver. 01.23.00 | 9/15/2022 | Q70 Ver. 01.22.00 | 8/1/2022 | Q70 Ver. 01.21.02 | 85.00 | 45 |
2 | c | 02.12.00 | 3/29/2023 | 00.43.00 | 1/18/2023 | 00.41.00 | 1/18/2023 | 00.33.01 | 70.00 | 0 |
3 | d | 10/9/2020 | 0.00.26 | N/A | 0 | |||||
5 | e | 80.3 | 11/6/2018 | 80.28 | 7/25/2014 | 0.18 | 1565.00 | 0 |
Hi,
This M code works
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZTNbsMgDMdfpcq5INuQAE8x9bBdqh62tadNmbRLX38mdreoqgKeFARy/JPh74/jccBhP7zyAvQUPQAfeRt54+/p8zyc9g+dDgl3L5dvv2MbKIGTQ3QEmA1sEhuODmJli4EtHohtVDSugUUQGwQHlSWwsUvc6ICsLF87aNzJyrKtuhDrDFZ2FBtlh+Y7Z40bRWdCA1s0v1F1pn6WNEdEjuUysig54qCQrWzQ2iiq1Zqt57dWwzxwOiToa5gWu9UwDXazCFqsxoVRBcV+djMZLZb+3lus7FYiaxe+15/kUWMA+HCbC6kfCdIe3M9S4qGJRNRH5X7k9pasQ6eF0DJrUOaj9M8aiXw+S9XWJ3uaVKjfyfI8f8xf1/kf3rW2L7wyi7MQS4ETZzDVio2ra9y58kZZtLzrjNMP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Device Serial" = _t, #"Device Name" = _t, #"Latest Bios Version" = _t, biosmfgver = _t, biosdate = _t, biosstatus = _t]),
#"Filtered Rows1" = Table.SelectRows(Source, each ([biosstatus] = "Old")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"biosstatus"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Device Serial", type text}, {"Device Name", type text}, {"Latest Bios Version", type text}, {"biosmfgver", type text}, {"biosdate", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Device Serial"}, {{"All", each _, type table [Device Serial=nullable text, Device Name=nullable text, Latest Bios Version=nullable text, biosmfgver=nullable text, biosdate=nullable date, biosstatus=nullable text]}}),
biosdaterank = Table.TransformColumns(#"Grouped Rows",{"All", each Table.AddRankColumn(_,"Daterank",
{"biosdate", Order.Descending},
[RankKind = RankKind.Competition]
)}),
#"Removed Columns" = Table.RemoveColumns(biosdaterank,{"Device Serial"}),
#"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"Device Serial", "Device Name", "Latest Bios Version", "biosmfgver", "biosdate", "Daterank"}, {"Device Serial", "Device Name", "Latest Bios Version", "biosmfgver", "biosdate", "Daterank"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded All", each [Daterank] <= 3),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Device Serial", "Device Name", "Latest Bios Version", "Daterank"}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","bios","",Replacer.ReplaceText,{"Attribute"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","mfg","",Replacer.ReplaceText,{"Attribute"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value1", {{"Daterank", type text}}, "en-IN"),{"Attribute", "Daterank"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value"),
Datecolumns = Table.FromList(List.Select(Table.ColumnNames(#"Pivoted Column"), each Text.Contains(_,"date",Comparer.OrdinalIgnoreCase)), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(Datecolumns, "Custom", each Value.Type(#date(2023,1,1))),
datatypelist = List.Zip({#"Added Custom"[Column1],#"Added Custom"[Custom]}),
Result = Table.TransformColumnTypes(#"Pivoted Column",datatypelist),
#"Datediff 1" = Table.AddColumn(Result, "Date Diff 1", each Duration.Days([date 1] - [date 2]), Int64.Type),
#"Datediff 2" = Table.AddColumn(#"Datediff 1", "Date Diff 2", each Duration.Days([date 2] - [date 3]), Int64.Type)
in
#"Datediff 2"
Hope this helps.
Hi Ashish, Thank you for this solution. I have a question;
Would this be possible to do in DAX and not in power Query?
If not, thank you.
Hi,
Here's a start. You may download my PBI file from here.
Hope this helps.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |