Last row of data for each ID sorted by Date&Time format

[Anonymous]

Hi

I have a large table containing many columns and over 7000 rows. 

I am interested in showing only 1 row of data for each ID in this case named: Vogn. 

This row must be the last row according to the date and the data that is to show is binary which

is of text format. This binary has all the information I need. Hence the reason for 1 row per Vogn.

I have uploaded the 3 columns data in this in txt format. 

column 1 is the date&time    and column 2 is the Vogn which is the ID and lastly column 3 is the data which contains the binary. 

Link: Click here for the data file

[Anonymous]

Hi @Anonymous ,

I dont have access to your file.

Would it be possible to uplaod it on Dropbox and than share the link here?

Thanks,

Tejaswi

[Anonymous]

Hi.

It should definitly work now.

[CheenuSing · Accepted solution]

Hi @Anonymous ,

The way to go about is doing it powerquery.

We sort the data on Vogn, then create a custom column which is grouped by Vogn and get the Maximum date by group.  This is done using GroupBy function.

This will result in a table.

Expend the table to get the remaining columns.

Then selectrows from the table where the ReceivedTimestampLocal equals LastDate.

Copy paste the following code in the queryeditor of the table in your pbix file.

Change the path for your csv file

let
    Source = Csv.Document(File.Contents("C:\PowerBICommunity\87ahybztkyoajyoz\Data.txt"),[Delimiter="	", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Sorted Rows" = Table.Sort(#"Promoted Headers",{{"Vogn", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Vogn"}, {{"LastDate", each List.Max([ReceivedTimestampLocal]), type text}, {"All", each _, type table [ReceivedTimestampLocal=text, Vogn=text, Dec2Bin_func=text]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"ReceivedTimestampLocal", "Dec2Bin_func"}, {"ReceivedTimestampLocal", "Dec2Bin_func"}),
    #"Final" = Table.SelectRows (#"Expanded All",each [ReceivedTimestampLocal] = [LastDate])
in
    Final

I am attaching a file for GroupBy creation steps.

Cheers

CheenuSing

[Anonymous]

Hi

It works thats the good part. However the "grouped by" 3 columns show as such in the Final. How about the rest of my columns which i did not "group by". It seems Power Bi no longer shows them. 

How to unfold them?

I tried many thing to bring them back but cannot.

This is how it looks before:

This is after:

[CheenuSing]

Hi @Anonymous ,

The data you provided had only 3 columns.

No to worry.

  Go to the Advanced Editor in Query view

In the step

source =

Csv.Document(File.Contents("C:\PowerBICommunity\87ahybztkyoajyoz\Data.txt"),[Delimiter=" ", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None])

Set the value to number of columns in your original table instead of 3 in the code I pasted.

At step Grouped By you will double arrows under the column All with value Table .

When you click on the double arrow all the coulmns should get displayed.

Check it out.

If this has worked please mark it as a solution and ofcourse KUDOS.

Cheers

[Anonymous]

let
    Source = Csv.Document(Web.Contents("http://something.com/data/glovadata/latest/24h/csv"),[Delimiter=";", Columns=25, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ReceivedTimestampLocal", type datetime}, {"ToiletId", type text}, {"GlovaTimeLocal", type datetime}, {"ModuleSn", type text}, {"FlushCounterController", Int64.Type}, {"FlushCntModule", Int64.Type}, {"Temperature", type number}, {"MainAirPressure", type number}, {"EvacuationPressure", type number}, {"VacuumAchieved", type number}, {"PressureAchieved1", type number}, {"PressureAchieved2", type number}, {"PressureTime1", type number}, {"PressureTime2", type number}, {"OutletTime", type number}, {"BoostPressure", type number}, {"EvacuationTime", type number}, {"CycleTime", type number}, {"TransmitterOffset", type number}, {"ToiletState", Int64.Type}, {"MessageType", Int64.Type}, {"FailState", Int64.Type}, {"FailCode", Int64.Type}, {"FailureDiagnostic", type text}, {"SWVersion", Int64.Type}}),
    #"Ekstraheret tekstinterval" = Table.TransformColumns(#"Changed Type", {{"ToiletId", each Text.Middle(_, 2, 4), type text}}),
    #"Omdøbte kolonner" = Table.RenameColumns(#"Ekstraheret tekstinterval",{{"ToiletId", "Litra"}}),
    #"Duplikerede kolonner" = Table.DuplicateColumn(#"Omdøbte kolonner", "GlovaTimeLocal", "GlovaTimeLocal - kopi"),
    #"Omdøbte kolonner1" = Table.RenameColumns(#"Duplikerede kolonner",{{"GlovaTimeLocal - kopi", "Tid-time"}}),
    #"Ændret type1" = Table.TransformColumnTypes(#"Omdøbte kolonner1",{{"Tid-time", type time}}),
    #"Duplikerede kolonner1" = Table.DuplicateColumn(#"Ændret type1", "Tid-time", "Tid-time - kopi"),
    #"Udtrukket minut" = Table.TransformColumns(#"Duplikerede kolonner1",{{"Tid-time - kopi", Time.Minute, Int64.Type}}),
    #"Udtrukket time" = Table.TransformColumns(#"Udtrukket minut",{{"Tid-time", Time.Hour, Int64.Type}}),
    #"Omdøbte kolonner2" = Table.RenameColumns(#"Udtrukket time",{{"Tid-time - kopi", "Tid-Minutter"}, {"GlovaTimeLocal", "Dato"}}),
    #"Ændret type2" = Table.TransformColumnTypes(#"Omdøbte kolonner2",{{"Dato", type date}}),
    #"Omdøbte kolonner3" = Table.RenameColumns(#"Ændret type2",{{"Tid-time", "KL.:"}, {"Tid-Minutter", "Minut"}, {"Litra", "Vogn"}}),
    #"Duplikerede kolonner2" = Table.DuplicateColumn(#"Omdøbte kolonner3", "Vogn", "Vogn - kopi"),
    #"Opdel kolonne efter placering" = Table.SplitColumn(#"Duplikerede kolonner2", "Vogn - kopi", Splitter.SplitTextByPositions({0, 2}, false), {"Vogn - kopi.1", "Vogn - kopi.2"}),
    #"Ændret type3" = Table.TransformColumnTypes(#"Opdel kolonne efter placering",{{"Vogn - kopi.1", Int64.Type}, {"Vogn - kopi.2", Int64.Type}}),
    #"Erstattet værdi" = Table.ReplaceValue(#"Ændret type3",21,20,Replacer.ReplaceValue,{"Vogn - kopi.1"}),
    #"Sammenflettede kolonner" = Table.CombineColumns(Table.TransformColumnTypes(#"Erstattet værdi", {{"Vogn - kopi.2", type text}, {"Vogn - kopi.1", type text}}, "da-DK"),{"Vogn - kopi.2", "Vogn - kopi.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Flettet"),
    #"Fjernede kolonner" = Table.RemoveColumns(#"Sammenflettede kolonner",{"Flettet"}),
    #"Ændret type4" = Table.TransformColumnTypes(#"Fjernede kolonner",{{"ReceivedTimestampLocal", type text}, {"Vogn", Int64.Type}}),
    #"Opdel kolonne efter afgrænser" = Table.SplitColumn(#"Ændret type4", "FailureDiagnostic", Splitter.SplitTextByEachDelimiter({"x"}, QuoteStyle.Csv, false), {"FailureDiagnostic.1", "FailureDiagnostic.2"}),
    #"Ændret type" = Table.TransformColumnTypes(#"Opdel kolonne efter afgrænser",{{"FailureDiagnostic.1", Int64.Type}, {"FailureDiagnostic.2", type text}}),
    #"Aktiveret brugerdefineret funktion" = Table.AddColumn(#"Ændret type", "Hex2Dec_func", each Hex2Dec_func([FailureDiagnostic.2])),
    #"Aktiveret brugerdefineret funktion1" = Table.AddColumn(#"Aktiveret brugerdefineret funktion", "Dec2Bin_func", each Dec2Bin_func([Hex2Dec_func], 2, 38)),
    #"Erstattede fejl" = Table.ReplaceErrorValues(#"Aktiveret brugerdefineret funktion1", {{"Dec2Bin_func", "0"}}),
    #"Ændret type5" = Table.TransformColumnTypes(#"Erstattede fejl",{{"Dec2Bin_func", type text}}),
    #"Grupperede rækker" = Table.Group(#"Ændret type5", {"Vogn"}, {{"LastDate", each List.Max([ReceivedTimestampLocal]), type text},{"All", each _, type table [ReceivedTimestampLocal=text, Vogn=text]}}),
    #"Udvidet All" = Table.ExpandTableColumn(#"Grupperede rækker", "All", {"ReceivedTimestampLocal", "Vogn"}, {"All.ReceivedTimestampLocal", "All.Vogn"}),
     #"Final" = Table.SelectRows (#"Udvidet All",each [All.ReceivedTimestampLocal] = [LastDate])
in
    Final

Here is the entire code. However it already showed column = 25 at source. So I dont think that is the issue.

I think the filtering that comes from Grouping is the cause.

[CheenuSing]

Hi @Anonymous 

You are right the filtering at Grouped By when expended you should add all the missing  columns.

Cheers

CheenuSing

[Anonymous]

How add them?

Can you unfilter them ?

[CheenuSing]

Hi @Anonymous ,

Yes you can select all columns or unfilter all of them .

Appreciate if you can accept it as solution.

Cheers

CheenuSing