cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ks1
Frequent Visitor

Grouping by three conditions

Hello,

 

I'd reatly appreciate some help with Power Query Grouping with data like that below, I'm trying to consildate/group to one row per ID, retaining the non-blank values in the Status and Result columns based on the latest dates and times, using the following heirachy of rules:

 

First: Non blank

Then: Latest / oldest 'Date' 

Then: Latest /oldest  date & time in the 'Record last updated' if there is more than one non-blank value on the same date

 

 

IDDateStatusResultRecord last updated
3390494422 Jul 20 Something else12 10 2020 18:29:31
3390494420 Jul 20This  12 10 2020 18:29:30
98741359704 Aug 20 Something else12 10 2020 18:30:18
98741359724 Jul 20 Something 12 10 2020 18:30:11
98741359722 Jul 20That 12 10 2020 18:30:00
98741359722 Jul 20This Something else12 10 2020 18:30:01
98741359719 Feb 20  12 10 2020 18:18:10
98741359719 Feb 20  12 10 2020 18:19:19
98741359706 Feb 20This Something ordinary12 10 2020 18:31:21
98741359706 Feb 20  12 10 2020 18:18:09
258321669424 Feb 20ThatSomething 12 10 2020 18:19:26
258321669419 Feb 20This Something else12 10 2020 18:19:25

 

 

I'm expecting to for the query to filter the above to this:

 

IDStatsResult
33904944This Something else
987413597This Something else
2583216694ThatSomething 

 

I'm new to this forum and tried posting this before but can't find it anywhere, so hopefully not cross-posted.

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

No. As I said earlier, null, blank/empty, and space are different. Here is the same code but I've replaced your <space> with ""

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZG7DoJAEEV/ZbK1xczs8lg6Gwtb7QgFRqIkCAmPwr93ARMgA0qyJAS4h3Nn4lhpbdFYY9RBMcO5K4DR3YO7LtUra595+YCsaDL3gBgI3XtGoDBiG2lSyWHJwIlxfeYNfFkyikPUhoEh7dnAfYMGjt1j9/81RhQKCJutEqsAkgCeN0jb1QIuirLAMjqW/18CpQNZOGW3qYQU6I8U2JGz7sjJ+1NOilf1PS/T+i3lKWIpP4dtyuMowV6omXzfmnF1M4th8j+255qwLyHzEezcQU/yVJJ8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Status = _t, Result = _t, #"Record last updated" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Record last updated", type datetime}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ","",Replacer.ReplaceValue,{"Status", "Result"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"ID"}, {{"All Rows", each _, type table [ID=nullable text, Date=nullable date, Status=nullable text, Result=nullable text, Record last updated=nullable datetime]}}),
    #"Added Status" = 
        Table.AddColumn(
            #"Grouped Rows", 
            "Status", 
            each
            Table.Max( 
                Table.FromRecords(
                    {
                        Table.Max(
                        Table.SelectRows([All Rows], each [Status] <> ""),
                        each [Date]
                        )
                    }
                ),
                each [Record last updated]
            )[Status]
        ),
    #"Added Result" = 
        Table.AddColumn(
            #"Added Status", 
            "Result", 
            each
            Table.Max( 
                Table.FromRecords(
                    {
                        Table.Max(
                        Table.SelectRows([All Rows], each [Result] <> ""),
                        each [Date]
                        )
                    }
                ),
                each [Record last updated]
            )[Result]
        ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Result",{"ID", "Status", "Result"})
in
    #"Removed Other Columns"

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

17 REPLIES 17
edhans
Super User III
Super User III

No. As I said earlier, null, blank/empty, and space are different. Here is the same code but I've replaced your <space> with ""

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZG7DoJAEEV/ZbK1xczs8lg6Gwtb7QgFRqIkCAmPwr93ARMgA0qyJAS4h3Nn4lhpbdFYY9RBMcO5K4DR3YO7LtUra595+YCsaDL3gBgI3XtGoDBiG2lSyWHJwIlxfeYNfFkyikPUhoEh7dnAfYMGjt1j9/81RhQKCJutEqsAkgCeN0jb1QIuirLAMjqW/18CpQNZOGW3qYQU6I8U2JGz7sjJ+1NOilf1PS/T+i3lKWIpP4dtyuMowV6omXzfmnF1M4th8j+255qwLyHzEezcQU/yVJJ8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Status = _t, Result = _t, #"Record last updated" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Record last updated", type datetime}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ","",Replacer.ReplaceValue,{"Status", "Result"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"ID"}, {{"All Rows", each _, type table [ID=nullable text, Date=nullable date, Status=nullable text, Result=nullable text, Record last updated=nullable datetime]}}),
    #"Added Status" = 
        Table.AddColumn(
            #"Grouped Rows", 
            "Status", 
            each
            Table.Max( 
                Table.FromRecords(
                    {
                        Table.Max(
                        Table.SelectRows([All Rows], each [Status] <> ""),
                        each [Date]
                        )
                    }
                ),
                each [Record last updated]
            )[Status]
        ),
    #"Added Result" = 
        Table.AddColumn(
            #"Added Status", 
            "Result", 
            each
            Table.Max( 
                Table.FromRecords(
                    {
                        Table.Max(
                        Table.SelectRows([All Rows], each [Result] <> ""),
                        each [Date]
                        )
                    }
                ),
                each [Record last updated]
            )[Result]
        ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Result",{"ID", "Status", "Result"})
in
    #"Removed Other Columns"

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

edhans
Super User III
Super User III

@ks1 - you can read a bit about List.Buffer here. You need to pay attention to how your data is coming into the model. I rarely sort data in Power Query for a few reasons:

  1. DAX doesn't care. It will change the sort anyway, or change how it is viewed in the Data view in Power BI. Same with the Data model in Excel.
  2. The sort can change based on earlier steps in your model. For example, if you were to merge data or append data into your data even after the sort operation, Power Query will internally optimize the code and may do things out of sequence to arrive at the same result faster. But it may change the sort order when it does this. Remember, this is a table of data, not a spreadsheet, so sorting isn't usually relevant. You have to use Table.Buffer, List.Buffer, or Binary.Buffer. Note that the point of buffering is to isolate the table at that point from external changes. You can read the documentation on Table.Buffer here, which is pretty sparse, but the key point is it "isolating it from external changes during evaluation."
  3. List.Buffer works slightly differently and does hold the list in memory for faster evaluation. I use List.Buffer all of the time within a List.Contains() where the query is folded back to the server. It fully generates the list, then creates the IN operator in SQL very quickly, vs one list item at a time.
  4. Bottom line is I generally only sort in PQ in two cases:
    1. I am dumping the result to an Excel table. There sort order can matter, and the Table.Sort function is always the last step in my query.
    2. I am using some of the List operations and doing some sort of running total based on date, volume, or something else, and I always use List.Buffer in those cases to ensure that all of my separate lists maintain the same order so when I put them back into a table, they are in the same order and become part of the correct record. I do this pretty rarely too as 99% of the time, running totals are best done in DAX, and there is no concept of sorting data in DAX at all. It is 100% based on how you filter the data. I try to apply that same principle to Power Query, which is what my proposed solution is based on.

So you can use the sort mechanism, but you need to understand that it does not work as a sort in a spreadsheet. It is somewhat volatile, and you need to know the impact of that and if/when/where to buffer.

 

This is why I used table filtering when I did this. I am controlling exactly what I am returning vs relying on it being in a specific order. That isn't to say you cannot sort and use List.First. As I said in my post earlier, I like @mahoneypat 's response and it is a clever use of sorting. You just have to fully understand all of the ramifications of that.

 

As for the error you got in my code when you replaced with null, I don't know. Here is my full code where I am replacing your " " <space> with a null, then I just changed the comparison to <> null and it worked first try. Perhaps you missed something in your copy and paste.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZG7DoJAEEV/ZbK1xczs8lg6Gwtb7QgFRqIkCAmPwr93ARMgA0qyJAS4h3Nn4lhpbdFYY9RBMcO5K4DR3YO7LtUra595+YCsaDL3gBgI3XtGoDBiG2lSyWHJwIlxfeYNfFkyikPUhoEh7dnAfYMGjt1j9/81RhQKCJutEqsAkgCeN0jb1QIuirLAMjqW/18CpQNZOGW3qYQU6I8U2JGz7sjJ+1NOilf1PS/T+i3lKWIpP4dtyuMowV6omXzfmnF1M4th8j+255qwLyHzEezcQU/yVJJ8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Status = _t, Result = _t, #"Record last updated" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Record last updated", type datetime}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ",null,Replacer.ReplaceValue,{"Status", "Result"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"ID"}, {{"All Rows", each _, type table [ID=nullable text, Date=nullable date, Status=nullable text, Result=nullable text, Record last updated=nullable datetime]}}),
    #"Added Status" = 
        Table.AddColumn(
            #"Grouped Rows", 
            "Status", 
            each
            Table.Max( 
                Table.FromRecords(
                    {
                        Table.Max(
                        Table.SelectRows([All Rows], each [Status] <> null),
                        each [Date]
                        )
                    }
                ),
                each [Record last updated]
            )[Status]
        ),
    #"Added Result" = 
        Table.AddColumn(
            #"Added Status", 
            "Result", 
            each
            Table.Max( 
                Table.FromRecords(
                    {
                        Table.Max(
                        Table.SelectRows([All Rows], each [Result] <> null),
                        each [Date]
                        )
                    }
                ),
                each [Record last updated]
            )[Result]
        ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Result",{"ID", "Status", "Result"})
in
    #"Removed Other Columns"

Again, if you will share your data via some sort of onedrive/dropbox link, I can connect this to your data and ensure it works.

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

  



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
ks1
Frequent Visitor

Thanks @edhans , I'll read up on the buffer and makes a lot of sense not to have to rely on a sorted list.  Ref space vs buffer, the trouble is in my real data, it isn't a space as in " ", but a "", which I can search for to replace with null I think? 

mahoneypat
Super User IV
Super User IV

Your explanation is correct, and List.Last would be same as List.First.  I don't think you should use List.Max as it may grab the last one alphabetically, but I didn't test that.  FYI too that, if this is slow at scale (it probably will be), you can speed things up a lot by using Table.Buffer or List.Buffer within the query to store a table/list that is referred to repeatedly (in each row).

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


ks1
Frequent Visitor

Thanks, I'll stick to the List.First approach then.

 

Could you point me to an example of this please?:

 

@mahoneypat wrote:

... you can speed things up a lot by using Table.Buffer or List.Buffer within the query to store a table/list that is referred to repeatedly (in each row).

 

ks1
Frequent Visitor

Thanks @mahoneypat , that produced the expected results.  I'm trying to understand the logic :

= Table.Group(#"Replaced Value", {"ID"}, {{"Status", each List.First(List.RemoveNulls([Status])), type nullable text}, {"Result", each List.First(List.RemoveNulls([Result])), type nullable text}})
in

I think List.First returns the first in a list and the List.RemoveNulls ignores the null/blank values, which relies on the list being sorted by date time highest first?  Presmumably List.Last would work on a list that was sorted ascending order?  But did you not suggest it because it is slower/ less efficient because it has to go to the bottom of the list?  Or doesn't it make any difference?

 

What I tried in the meantime was this:

Table.Group(#"Changed Type", {"ID"}, {{"Result of Referral", each List.Max([Result]), type nullable text}, {"Status", each List.Max([Status]), type nullable text}}),

It seems to produce the same results but without the list being sorted.   But is it problematic?  Would it be also less efficient? 

 

With your solution @edhans,  modified from " " to ""  I got a Expression.Error: We cannot convert the value null to type Record. Details: Value= Type=[Type].

 

The same happens even if I replace the "" with null before the Added Status step:

 #"Grouped Rows" = Table.Group(#"Replaced Value", {"ID"}, {{"All Rows", each _, type table [ID=nullable text, Date=nullable date, Status=nullable text, Result=nullable text, Timestamp=nullable datetime]}}),
   #"Added Status" = 
        Table.AddColumn(
            #"Grouped Rows", 
            "Status", 
            each
            Table.Max( 
                Table.FromRecords(
                    {
                        Table.Max(
                        Table.SelectRows([All Rows], each [Status] <> null),
                        each [Date]
                        )
                    }
                ),
                each [Timestamp]
            )[Status]
        ),
    #"Added Result" = 
        Table.AddColumn(
            #"Added Status", 
            "Result", 
            each
            Table.Max( 
                Table.FromRecords(
                    {
                        Table.Max(
                        Table.SelectRows([All Rows], each [Result] <> null),
                        each [Date]
                        )
                    }
                ),
                each [Timestamp]
            )[Result]
        ),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Result",{"ID", "Status", "Result"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns1", each true)
in
    #"Filtered Rows"

 




 

edhans
Super User III
Super User III

@ks1 my formula is looking for a space, because that is what your original data had. Replace the two parts of the formula that look like this:

<> " ")

with this if you are getting blanks (empty) cells - no space between the quotes

<> "")

. or with this if you are getting null values in the data you do not want.

<> null)

Nulls, blanks, and spaces are all very different in Power Query. Nulls are the best to work with if you can get it because the software shows you nulls. Spaces and empty (blanks) visually look the same because spaces are invisible. You can tell something is empty vs a space if you click on it, and in the bottom window you see a cursor, but you cannot move the cursor. it just binks. If you can move it left/right, then it is spaces.

edhans_0-1607991286415.png

 

Null is again the best option because it shows "null" in the window below too.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User III
Super User III

If you are unable to post data via the table format in the link below @ks1 , just share an Excel file with the data via OneDrive or Dropbox.

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
mahoneypat
Super User IV
Super User IV

The reason you were seeing blank is likely because the List.Select was looking for " " which is what your example data had, but your real data probably does not.  Also, the more recent example data didn't have any status/result values for one the IDs.  I added some values and updated it to first replace blanks with null and used List.RemoveNulls instead.  You just need to make sure blank values are replaced with null prior to the custom step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLQ9SrN0TUyUNJRMja2NDCxNDHRCzIE8iDI0EDf0EjfyMDIQMHQwsrIUilWh4Cm4PzcVIXiksSS0mKc+o1IsdTYgBaaQjIyixVKUitKwOzEEhgbuzHG5NhtQhW7TXQdS9OJttoQrMfQTDc4tQCiycjYxNTAyMTYDKILHD0hEPswtZsS0g5E/iUZqUU4nAzUHwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t, Result = _t, Status = _t, Timestamp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ID", type text}, {"Result", type text}, {"Status", type text}, {"Timestamp", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}, {"Timestamp", Order.Descending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows","",null,Replacer.ReplaceValue,{"Result", "Status"}),
    Custom1 = Table.Group(#"Replaced Value", {"ID"}, {{"Status", each List.First(List.RemoveNulls([Status])), type nullable text}, {"Result", each List.First(List.RemoveNulls([Result])), type nullable text}})
in
    Custom1

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


ks1
Frequent Visitor

@edhans, for some reason I'm not getting the expected outcome with my real data.

 

For example in the following selection, for the ID "33904944.R1" the code brings back blanks in both result and status columns (I've replaced the confidential with "This Text" and "That Text", but the values for 22 Jul and 24 Jul are in fact the same.)

Date ID Result Status Timestamp
20-Jul-20 33904944.R1 12 Oct 20 18:29:30
20-Jul-20 33904944.R1 12 Oct 20 18:29:31
22-Jul-20 33904944.R1 12 Oct 20 18:30:00
22-Jul-20 33904944.R1 12 Oct 20 18:30:01
22-Jul-20 33904944.R1 This text That text 12 Oct 20 18:30:07
23-Jul-20 33904944.R1 12 Oct 20 18:30:11
24-Jul-20 33904944.R1 This text That text 12 Oct 20 18:30:18
4-Aug-20 33904944.R1 12 Oct 20 18:31:21
16-Sep-20 234502436.R1 12 Oct 20 18:35:46
16-Sep-20 234502436.R1 12 Oct 20 18:35:47

 

I adjusted one the your steps only to reflect the diffetent fieldname for the timestamp, but maybe I broke something else?  

 

 

 

    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Timestamp", type datetime}, {"Result", type text}, {"Status", type text}}),

   #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"All Rows", each _, type table [ID=nullable text, Date=nullable date, Status=nullable text, Result=nullable text, Timestamp=nullable datetime]}}),
   #"Added Status" = 
        Table.AddColumn(
            #"Grouped Rows", 
            "Status", 
            each
            Table.Max( 
                Table.FromRecords(
                    {
                        Table.Max(
                        Table.SelectRows([All Rows], each [Status] <> " "),
                        each [Date]
                        )
                    }
                ),
                each [Timestamp]
            )[Status]
        ),
    #"Added Result" = 
        Table.AddColumn(
            #"Added Status", 
            "Result", 
            each
            Table.Max( 
                Table.FromRecords(
                    {
                        Table.Max(
                        Table.SelectRows([All Rows], each [Result] <> " "),
                        each [Date]
                        )
                    }
                ),
                each [Timestamp]
            )[Result]
        ),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Result",{"ID", "Status", "Result"})
in
    #"Removed Other Columns1"

 

 

 

 

(I'm sorry for the table format - I'm really struggling with this forum software - "Your post has been changed because invalid HTML was found in the message body" thing, when I didn't put any HTML in inthe first place - then it tells me the invalid HTML has been removed - try again.  Then it tells me "Post flooding detected" Iand I have to wait an hour?)

edhans
Super User III
Super User III

Also @ks1 I have a question.
I liked the simplicty of sorting that @mahoneypat did, but I wanted to see if it would break. Please look at this table and tell me what you expect. I circled what I expected the result to be

edhans_0-1607973452757.png

If I redid @mahoneypat's code correctly, it returns this:

edhans_1-1607973585630.png

Mine, which relies on Table.SelectRows() vs List.First() returns this:

edhans_2-1607973624471.png

 

What are you expecting here? 

Pat, here is your code back - I do not want to misrepresent what you've done.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZI9D4IwEIb/yoWZ4e5aPsrm4uDgopthwEiABMHwMfjvrWAC5FBJ2qRp+7593rteLo5SBrXR2nEdZjj0JTDaNdh5qu9plxdVBmnZpnaDGAjtOSNQGLGJFDmxu/TAyeOcFy18vKQUB6kJA03KM4G9gxp2fbb5fYURhcKE9bcQqwYkDXieIOlWA1gpygBL6Rj+fwiUDGRgn16nEBLgPSTABp2xQ1ben3QSvG5uRZU0TwlPEUv4udlXeBwh2AsVk+8bPbZuRjFU/kf3bBL2pcm8BBt78Hby/v7GY/1YaR8N/yB+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Status = _t, Result = _t, #"Record last updated" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Status", type text}, {"Result", type text}, {"Record last updated", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}, {"Record last updated", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Status", each List.First(List.Select([Status], each _ <> " ")), type nullable text}, {"Result", each List.First(List.Select([Result], each _ <> " ")), type nullable text}})
in
    #"Grouped Rows"

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
ks1
Frequent Visitor

Hello @edhans, yes in answwr to your questions, I would expect the results you have circled.  

 

For some reason, when I tried to apply @mahoneypat 's code it returned only blanks in my full data set.  I'm not sure why (Iliked the simplicity too.

 

With your code, I'm just trying to work out whether the results are coming out as I expect in the full data set.  Will revert shortly.

 

Thanks boths for your help

 

 

 

edhans
Super User III
Super User III

@ks1 - using my code or @mahoneypat you need to integrate the M code. See this article and steps:

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
ks1
Frequent Visitor

Hi Many thanks for your reply.

 

I'm trying to fathom it by applying it to my data.  First I'm confused about the bit before the grouping 

 

My source is another query so I make the first lines of my query like this:

let
Source = #"q Events in report Range",
#"Removed Other Columns" = Table.SelectColumns(Source,{"Date", "ID", "Result", "Status", "Timestamp"}),

 

I think your line below your source is setting a variable of some kind and needs to come at the top, right after the source statement, but when try to do that I get an error "Token idenfifier expected" and squiggly line under the 'let' in "let _t"...

let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Status = _t, Result = _t, #"Record last updated" = _t]),

 

 

 

 

 

mahoneypat
Super User IV
Super User IV

Here is one way to do it in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  The key part is the custom #"Grouped Rows" step (after sorting by the two date columns descending).

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZG7DoJAEEV/ZbK1xczs8lg6Gwtb7QgFRqIkCAmPwr93ARMgA0qyJAS4h3Nn4lhpbdFYY9RBMcO5K4DR3YO7LtUra595+YCsaDL3gBgI3XtGoDBiG2lSyWHJwIlxfeYNfFkyikPUhoEh7dnAfYMGjt1j9/81RhQKCJutEqsAkgCeN0jb1QIuirLAMjqW/18CpQNZOGW3qYQU6I8U2JGz7sjJ+1NOilf1PS/T+i3lKWIpP4dtyuMowV6omXzfmnF1M4th8j+255qwLyHzEezcQU/yVJJ8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Status = _t, Result = _t, #"Record last updated" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Status", type text}, {"Result", type text}, {"Record last updated", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}, {"Record last updated", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Status", each List.First(List.Select([Status], each _ <> " ")), type nullable text}, {"Result", each List.First(List.Select([Result], each _ <> " ")), type nullable text}})
in
    #"Grouped Rows"

 

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


ks1
Frequent Visitor

Hello Pat,  thanks for your reply.

 

I'm afraid I'ma  beginner and still don't understand how to get past the problem of the the Token Identifer expected when I put a 'let' statment after my 'Source' statement  

edhans
Super User III
Super User III

Hi @ks1 - 

There may be a quicker way to do this. I'd have to think on it, but what I've done is repeatedly filtered the results after grouping to get the max date, then max last modified date, and ignored rows where the status or result equals a space, which is what you have in your original data. It isn't blank, or "", but " "

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZG7DoJAEEV/ZbK1xczs8lg6Gwtb7QgFRqIkCAmPwr93ARMgA0qyJAS4h3Nn4lhpbdFYY9RBMcO5K4DR3YO7LtUra595+YCsaDL3gBgI3XtGoDBiG2lSyWHJwIlxfeYNfFkyikPUhoEh7dnAfYMGjt1j9/81RhQKCJutEqsAkgCeN0jb1QIuirLAMjqW/18CpQNZOGW3qYQU6I8U2JGz7sjJ+1NOilf1PS/T+i3lKWIpP4dtyuMowV6omXzfmnF1M4th8j+255qwLyHzEezcQU/yVJJ8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Status = _t, Result = _t, #"Record last updated" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Record last updated", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"All Rows", each _, type table [ID=nullable text, Date=nullable date, Status=nullable text, Result=nullable text, Record last updated=nullable datetime]}}),
    #"Added Status" = 
        Table.AddColumn(
            #"Grouped Rows", 
            "Status", 
            each
            Table.Max( 
                Table.FromRecords(
                    {
                        Table.Max(
                        Table.SelectRows([All Rows], each [Status] <> " "),
                        each [Date]
                        )
                    }
                ),
                each [Record last updated]
            )[Status]
        ),
    #"Added Result" = 
        Table.AddColumn(
            #"Added Status", 
            "Result", 
            each
            Table.Max( 
                Table.FromRecords(
                    {
                        Table.Max(
                        Table.SelectRows([All Rows], each [Result] <> " "),
                        each [Date]
                        )
                    }
                ),
                each [Record last updated]
            )[Result]
        ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Result",{"ID", "Status", "Result"})
in
    #"Removed Other Columns"

 

 

SO 

edhans_0-1607968699206.png

becomes this:

edhans_1-1607968719065.png

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

 

Both the Added Status and Added Result steps are identical in that they go back to the original Grouping. They just focus on the Status/Result columns as applicable.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors