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 want to create a Table where i can see all my Products with their last sale date. Also the Daterange should be choosable.
I tried it and it is not possible with a standard date-range filter.
So my second idea is to create a nother Table with the product number and the last sale date.
Does anyone have an idea how to to this?
I hope you have some ideas or tips how to solve this problem.
Here is some sample data from the salesline table:
ProductNumber | ProductName | Date |
123 | Pencil | 09.12.2019 |
543677 | PC | 16.12.2019 |
4567 | Keyboard | 05.12.2019 |
123 | Pencil | 05.12.2019 |
543677 | PC | 15.12.2019 |
543677 | PC | 14.12.2019 |
4567 | Keyboard | 12.12.2019 |
123 | Pencil | 01.12.2019 |
123 | Pencil | 02.12.2019 |
Solved! Go to Solution.
Hello @leschi ,
you can apply some simple grouping. But this will not fit your needs as you can't choose the daterange.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRCkjNS87MATIMLPUMjfSMDAwtlWJ1opVMTYzNzM1BCpyBhKEZiqSJqRlIyju1Mik/sSgFpNsURQG62ab4zMYraULAYqAsPosN8coi6Y0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProductNumber = _t, ProductName = _t, Date = _t]),
ChangeType = Table.TransformColumnTypes(Source,{{"ProductNumber", Int64.Type}, {"ProductName", type text}, {"Date", type date}}),
GroupedRows = Table.Group
(
ChangeType,
{"ProductNumber", "ProductName"},
{{"MaxDate", each List.Max([Date]), type date}}
)
in
GroupedRows
In Power BI is no way to include a external parameter in Power Query. This could be done in Excel. Using Power BI the only way would be to use DAX
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Hello @leschi ,
you can apply some simple grouping. But this will not fit your needs as you can't choose the daterange.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRCkjNS87MATIMLPUMjfSMDAwtlWJ1opVMTYzNzM1BCpyBhKEZiqSJqRlIyju1Mik/sSgFpNsURQG62ab4zMYraULAYqAsPosN8coi6Y0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProductNumber = _t, ProductName = _t, Date = _t]),
ChangeType = Table.TransformColumnTypes(Source,{{"ProductNumber", Int64.Type}, {"ProductName", type text}, {"Date", type date}}),
GroupedRows = Table.Group
(
ChangeType,
{"ProductNumber", "ProductName"},
{{"MaxDate", each List.Max([Date]), type date}}
)
in
GroupedRows
In Power BI is no way to include a external parameter in Power Query. This could be done in Excel. Using Power BI the only way would be to use DAX
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Thanks for your help !!
with some modifications it worked like a charm 😉
Covering 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.