Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
ID | Date | Status | Result | Record last updated |
33904944 | 22 Jul 20 | Something else | 12 10 2020 18:29:31 | |
33904944 | 20 Jul 20 | This | 12 10 2020 18:29:30 | |
987413597 | 04 Aug 20 | Something else | 12 10 2020 18:30:18 | |
987413597 | 24 Jul 20 | Something | 12 10 2020 18:30:11 | |
987413597 | 22 Jul 20 | That | 12 10 2020 18:30:00 | |
987413597 | 22 Jul 20 | This | Something else | 12 10 2020 18:30:01 |
987413597 | 19 Feb 20 | 12 10 2020 18:18:10 | ||
987413597 | 19 Feb 20 | 12 10 2020 18:19:19 | ||
987413597 | 06 Feb 20 | This | Something ordinary | 12 10 2020 18:31:21 |
987413597 | 06 Feb 20 | 12 10 2020 18:18:09 | ||
2583216694 | 24 Feb 20 | That | Something | 12 10 2020 18:19:26 |
2583216694 | 19 Feb 20 | This | Something else | 12 10 2020 18:19:25 |
I'm expecting to for the query to filter the above to this:
ID | Stats | Result |
33904944 | This | Something else |
987413597 | This | Something else |
2583216694 | That | Something |
I'm new to this forum and tried posting this before but can't find it anywhere, so hopefully not cross-posted.
Solved! Go to Solution.
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"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingNo. 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"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks @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?
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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).
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"
@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.
Null is again the best option because it shows "null" in the window below too.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIf 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThe 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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?)
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
If I redid @mahoneypat's code correctly, it returns this:
Mine, which relies on Table.SelectRows() vs List.First() returns this:
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"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHello @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
@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi 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]),
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
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
becomes this:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.