Forum Discussion
Measure value for first item only
Please define "First Row" - there is no built-in concept for that in Power Query. Do you mean sorted by DN#?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc1BCoMwEIXhu8xa6GQmMyZLabEFEzALLSK5/zWqllio4u7Bx8+bZzDs2DBqTVBB7DBxCLdm2YKQq6MPlx6n6z5O7z9PzUPIe63t6s8+FLfuxGPbnbj1anjzPmFxQ7sLMimunnTg8b45/3qx6IS+/6/Sk0DOHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order No" = _t, #"DN#" = _t, #"Order Total Weight" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Total Weight", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Order Total Weight", "Total Weight"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Order Total weight", each if [Index]>0 and #"Added Index"[Order No]{[Index]-1}=[Order No] then null else [Total Weight]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Order No", "DN#", "Total Weight", "Order Total weight"})
in
#"Removed Other Columns"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Hi Ibendlin,
thank you for a prompt response.
However, what I was actually looking for a DAX solution, not Power Query.
Regarding your question about "first row", please note that by "first row" I mean the first row/occurence of a given Order No. In an Excel pivot table, when you place an item in the Rows section, Excel (by default) does not repeat the item labels when data for a given item is displayed in more than one row.
I need to replicate exactly the same concept, but for an item placed in the Values section (show the item's value only in the first row with data for that item). I hope I have made myself clear now.
Kind regards,
IM
- lbendlin2 years ago
Super User
I mean the first row/occurence of a given Order NoDAX has no concept of that "first occurrence" - you need to bring your own index column (or other logic), same like I showed for Power Query.
- IgorM2 years agoFrequent Visitor
Hi Ibendlin,
I realise that DAX has no concept of row ordering but for the purpose of this query to identify the first row for a given Order No I was thinking of using the RANK function.
Regards,
- lbendlin2 years ago
Super User
There is also a ROWNUMBER function but the same caveat applies - it is non-deterministic.