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.
Hi there,
I hope you are all doing well!
I have a table with a lot of columns, coming from our ERP. I want to use Power Query so that everytime I pull the data from it and insert it in a specific folder, it'll instantly format it and be ready to use.
I am saying that as an introduction because I am able to get the result I want by using helper columns in Excel, but can't exactly replicate one helper column in Power Query.
Well, this is my sample data I get from ERP:
Product ID | Date | Pallets |
2505 | 18/03/21 | 5 |
2505 | 18/03/21 | 6 |
2505 | 20/03/21 | 4 |
33650 | 01/10/21 | 2 |
33650 | 21/10/21 | 33 |
20000 | 12/12/21 | 10 |
For some reasons, I only want to get the last row of each month as in - using helper columns in Excel (in italic below) :
Product ID | Date | Pallets | Index | Product-Month Concatenate | Month End | Date to use |
2505 | 18/03/21 | 5 | 1 | 25053 | 18/03/21 | |
2505 | 18/03/21 | 6 | 2 | 25053 | 18/03/21 | |
2505 | 20/03/21 | 4 | 3 | 25053 | Month End | 20/03/21 |
33650 | 01/10/21 | 2 | 4 | 3365010 | 01/10/21 | |
33650 | 21/10/21 | 33 | 5 | 3365010 | Month End | 21/10/21 |
20000 | 12/12/21 | 10 | 6 | 2000012 | Month End | 12/12/21 |
The column Month End is really important as I only look at the pallets we have at the end of the month. It helps in understanding our stocks on hand, and I can use it as a filter to get the Date to use.
Index is a simple index - I was able to recreate this column in Power Query
Concatenate - I was able to recreate this column in Power Query
Month End, however is a different task.
In Excel I'd use = IF(MAXIFS(Table1[Index],Table1[Product-Month Concatenate],Table1[Product-Month Concatenate])=Table1[Index],"Month End","")
Is there any way to M Code my way through this ? I do not want to use the group by feature as I fear I'd lose all the other columns - not present in this sample data.
Thank you very much in advance for the help provided!
Regards,
Marcel
Solved! Go to Solution.
Here you go @Marcel_
Here is what I did
By using the Group By feature, you are partitioning the data per product ID. That will make it perform better over large data sets. On 1,000 records it won't matter, but above that and you'll see performance gains with Group By.
Full code here.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY1LCsAwCESvUlwHHMcaepeQ+1+j1kA/dHDhvAc6hjAQ0sQOhSttqyQIme1nc+1vTtx8L+7eA9lgaliCH8FHuK9TyFw/qDllDDLnCQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, Date = _t, Pallets = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Pallets", Int64.Type}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-BM"),
#"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Product ID"}, {{"All Rows", each _, type table [Product ID=nullable text, Date=nullable date, Pallets=nullable number]}, {"Max Date", each List.Max([Date]), type nullable date}}),
#"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Pallets"}, {"Date", "Pallets"}),
#"Added Month End" = Table.AddColumn(#"Expanded All Rows", "Month End", each if [Date] = [Max Date] then "Month End" else null, type text),
#"Removed Other Columns" = Table.SelectColumns(#"Added Month End",{"Product ID", "Date", "Pallets", "Month End"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Pallets", Int64.Type}})
in
#"Changed Type1"
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 guys, sorry for the delay.
I have tried both and it worked so fine.. amazing. Thank you so much!
Cheers,
Marcel
Great @Marcel_ - glad you have a working solution.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThis would be done more easily with a DAX measure or column, but here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY1LCsAwCESvUlwHHMcaepeQ+1+j1kA/dHDhvAc6hjAQ0sQOhSttqyQIme1nc+1vTtx8L+7eA9lgaliCH8FHuK9TyFw/qDllDDLnCQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, Date = _t, Pallets = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "aa-DJ"),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Pallets", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Month End", each let
thisproduct = [Product ID],
maxdate = List.Max(Table.SelectRows(#"Changed Type", each [Product ID] = thisproduct)[Date])
in
if [Date] = maxdate then "Month End" else "", type text)
in
#"Added Custom"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Try it using the Group By feature. Add a final aggregation that is "All Rows" - this will preserve all of your data and you can re-expand it at the end of the process. If that doesn't work, or you need help with this, let us know.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans,
Thanks. I have tried the Group By feature but I am not really sure I am using it correctly, as I am trying to retrieve the last value at the end of each month.
i.e :
Product ID | Date | Pallets | Index | Product-Month Concatenate | Month End | Date to use |
2505 | 18/03/21 | 5 | 1 | 25053 | 18/03/21 | |
2505 | 18/03/21 | 6 | 2 | 25053 | 18/03/21 | |
2505 | 20/03/21 | 4 | 3 | 25053 | Month End | 20/03/21 |
33650 | 01/10/21 | 2 | 4 | 3365010 | 01/10/21 | |
33650 | 21/10/21 | 33 | 5 | 3365010 | Month End | 21/10/21 |
20000 | 12/12/21 | 10 | 6 | 2000012 | Month End | 12/12/21 |
1000 | 01/02/22 | 5 | 7 | 10001 | 01/02/22 | |
1000 | 01/02/22 | 5 | 8 | 10001 | 01/02/22 | |
1000 | 01/02/22 | 7 | 9 | 10001 | Month End | 01/02/22 |
Thanks for the All Rows tip!
Regards,
Marcel
Here you go @Marcel_
Here is what I did
By using the Group By feature, you are partitioning the data per product ID. That will make it perform better over large data sets. On 1,000 records it won't matter, but above that and you'll see performance gains with Group By.
Full code here.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY1LCsAwCESvUlwHHMcaepeQ+1+j1kA/dHDhvAc6hjAQ0sQOhSttqyQIme1nc+1vTtx8L+7eA9lgaliCH8FHuK9TyFw/qDllDDLnCQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, Date = _t, Pallets = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Pallets", Int64.Type}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-BM"),
#"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Product ID"}, {{"All Rows", each _, type table [Product ID=nullable text, Date=nullable date, Pallets=nullable number]}, {"Max Date", each List.Max([Date]), type nullable date}}),
#"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Pallets"}, {"Date", "Pallets"}),
#"Added Month End" = Table.AddColumn(#"Expanded All Rows", "Month End", each if [Date] = [Max Date] then "Month End" else null, type text),
#"Removed Other Columns" = Table.SelectColumns(#"Added Month End",{"Product ID", "Date", "Pallets", "Month End"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Pallets", Int64.Type}})
in
#"Changed Type1"
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 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.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |