Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi!
I honestly searched, but couldn't come up with something that I could apply.
I have a long list, in which different versions of the same dataset are distinguished by the column OP_Name. What I want is to always have only the latest version loaded into the report.
As I figured the OP_Name column wouldn't be suitable, I created the Version column and would filter for the highest number.
Probably super easy, but please help me out. 🙂
Thanks!
Solved! Go to Solution.
I'm not 100% clear what you need @micsafdas but look at this:
From that, I can just keep the data that is related to the max value in the version column using this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjCwUNJRAlEKJanFJUqxOlBhc4iwOZowmupYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Version = _t, OP_Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Version", Int64.Type}}),
MaxValue =
List.Distinct(
Table.SelectRows(
#"Changed Type",
each
let
varMaxItem = List.Max(#"Changed Type"[Version])
in
[Version] = varMaxItem
)[OP_Name]
),
TotalData =
Table.SelectRows(
#"Changed Type",
each List.Contains(MaxValue, [OP_Name])
)
in
TotalData
The MaxValue step finds the value for the OP_Name that is represented by the max value in the Value column.
The TotalData step filters the original table at #Changed Step by the text returned by the MaxValue step.
This is the result:
If that isn't what you need, please provide sample data with a clear explanation of expected output.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
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, or share an Excel/CSV file via OneDrive, Dropbox, etc.. 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.
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 ReportingHello @micsafdas
Hope I got you right... not quite clear, what you exactly need
you can group your data by OP_Name and apply a Table.Max on your version-column. After that you expand the record found. Here an example
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjAwV9JRclSK1aE+1wLIdcLJtYRwYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Version = _t, OP_Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Version", Int64.Type}, {"OP_Name", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"OP_Name"}, {{"AllRows", each Table.Max(_,"Version") }}),
#"Expanded AllRows" = Table.ExpandRecordColumn(#"Grouped Rows", "AllRows", {"Version"}, {"Version"})
in
#"Expanded AllRows"
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
@micsafdas there are probably a number of ways to do it, and I did it the way I did because I was filtering on the text value based on the maximum value in the numeric field. That may not be necessary, and if you just want to filter for the max numeric value, the simple Table.SelectRows() step you have will work. But your original post said you were filtering on the OP_Name field, so that is why I took the approach I did.
By the way, please also accept @Jimmy801 post as a solution if it works for you or helped. A thread can have 2+ solutions.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you both!
First of all: I'm sorry for not being clear on the expected output. I hope to do better next time.
Having said that, I can announce a success. 🙂
Although edhans did understand correctly what I wanted to achieve, I wasn't quite sure what to do with the code (although I do think I understand now). I'm more a GUI kinda guy. 😉
It was in fact the reply of Jimmy who inspired me to do it "my way" and using the group function. I do need to give the "Accept solution" to edhans though, sorry Jimmy. 🙂
What do I have now:
Expected was in fact that only records with Version=202008 will be left standing.
1. I grouped Version using Operation = "All rows".
2. I then filtered on Version using List.Max(#Grouped Rows"[Version])
3. Expanded the Column again.
Thanks for the help! I must say, this community is pretty awesome.
EDIT:
Okay, stupid me. Why go through the grouping at all?
The filtered rows step in Line 17 alone does the trick for me. Am I wrong?
Hello @micsafdas
Hope I got you right... not quite clear, what you exactly need
you can group your data by OP_Name and apply a Table.Max on your version-column. After that you expand the record found. Here an example
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjAwV9JRclSK1aE+1wLIdcLJtYRwYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Version = _t, OP_Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Version", Int64.Type}, {"OP_Name", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"OP_Name"}, {{"AllRows", each Table.Max(_,"Version") }}),
#"Expanded AllRows" = Table.ExpandRecordColumn(#"Grouped Rows", "AllRows", {"Version"}, {"Version"})
in
#"Expanded AllRows"
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
I'm not 100% clear what you need @micsafdas but look at this:
From that, I can just keep the data that is related to the max value in the version column using this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjCwUNJRAlEKJanFJUqxOlBhc4iwOZowmupYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Version = _t, OP_Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Version", Int64.Type}}),
MaxValue =
List.Distinct(
Table.SelectRows(
#"Changed Type",
each
let
varMaxItem = List.Max(#"Changed Type"[Version])
in
[Version] = varMaxItem
)[OP_Name]
),
TotalData =
Table.SelectRows(
#"Changed Type",
each List.Contains(MaxValue, [OP_Name])
)
in
TotalData
The MaxValue step finds the value for the OP_Name that is represented by the max value in the Value column.
The TotalData step filters the original table at #Changed Step by the text returned by the MaxValue step.
This is the result:
If that isn't what you need, please provide sample data with a clear explanation of expected output.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
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, or share an Excel/CSV file via OneDrive, Dropbox, etc.. 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.
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 ReportingStarting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
32 | |
16 | |
12 | |
11 | |
9 |
User | Count |
---|---|
44 | |
24 | |
20 | |
14 | |
14 |