Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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

 

1 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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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
Not applicable

Hi.

It should definitly work now.

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Anonymous
Not applicable

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:

 

image.png

 

This is after:

image.png

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

 

 

 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Anonymous
Not applicable

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.

Hi @Anonymous 

 

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

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Anonymous
Not applicable

How add them?

Can you unfilter them ?

 

Hi @Anonymous ,

 

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

 

Appreciate if you can accept it as solution.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors