Displaying 3 last values in comma separated list individually/calculate

[Anonymous]

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.

[Ahmedx]

Is this what you are looking for?

https://1drv.ms/u/s!AiUZ0Ws7G26RiCaxps0p6HYQjEBY?e=ONgevP

[Ahmedx]

you can get last 3 records in power query like this

List.LastN(
Text.Split([BIOSDates],","),3)

[Ashish_Mathur]

Hi,

I am not clear about he expected result.  Share the raw dataset and populate the table of expected results.

[Anonymous]

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

[Ashish_Mathur · Accepted 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.

[Anonymous]

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.

[Ashish_Mathur]

Hi,

Here's a start.  You may download my PBI file from here.

Hope this helps.