Power query - query first of prioritized group

[michiel_soede]

All,


I have attached simplified data set.

[TABLE="width: 302"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Asset[/TD]
[TD]Status[/TD]
[TD]Priority[/TD]
[TD]Timestamp[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Terminate[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]13-Oct-18[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]New[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]13-Oct-18[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Terminate[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]13-Oct-18[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Change[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13-Oct-18[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Terminate[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]13-Oct-18[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Change[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13-Oct-18[/TD]
[/TR]
</tbody>[/TABLE]



Is it possible in power query to select / query the first asset after I have sorted on priority? I can't simply select the status where I don't have the same transaction types for all assets (so for one asset I have new and terminated and for others ceased and new for example). Therefore I need to pick the first prioritized transaction per group of transaction for each asset.


Thanks for your help


Michiel

 

[VBA Geek]

Are you looking for something like this?

let
    // load your table
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpJLcrNzEssSQWyDUHYWNc/uUTX0EIpVgeiwi+1HEiaYMg5EdQNUuGckZiXDpI2xpB2JmiAMx4DYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Asset = _t, Status = _t, Priority = _t, Timestamp = _t]),
    
    
    ChangedType = Table.TransformColumnTypes(Source,{{"Asset", type text}, {"Status", type text}, {"Priority", Int64.Type}, {"Timestamp", type date}}),
    
    GrpTable = Table.Group( 
                    ChangedType, 
                    {"Asset"}, 
                    { {
                        "EntireTable", 
                        each Table.Sort( Table.RemoveColumns( _, {"Asset"} ), {"Priority", Order.Ascending}){0},
                        type record} 
                    },
                    GroupKind.Global
    ),
    ExpandedEntireTable = Table.ExpandRecordColumn(GrpTable, "EntireTable", {"Status", "Priority", "Timestamp"}, {"Status", "Priority", "Timestamp"})
in
    ExpandedEntireTable

 

[michiel_soede]

Thanks for your reaction. My understanding of M is still in a beginner's mode but if you say load your table:

[COLOR=#333333]Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpJLcrNzEssSQWyDUHYWNc/uUTX0EIpVgeiwi+1HEiaYMg5EdQNUuGckZiXDpI2xpB2JmiAMx4DYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Asset = _t, Status = _t, Priority = _t, Timestamp = _t]),
[/COLOR][COLOR=#333333][/COLOR]

What does that line exactly do and with which part do you then pull data from your actual source (in my case being source = TBL_SOURCE_LL_B2B_FVODA -> coming from another query)?

grateful for your response,

Michiel

 

[VBA Geek]

You need to replace the line you mentioned with the line you use to import data into PowerQuery

 

[michiel_soede]

Great, I have it working (in the sense of spitting out data) but not 100% sure yet if it behaves exactly as I want!
I need to study it a bit and let you know but wanted to thank you already for your response!

Regards,

Michiel

 

[michiel_soede]

Hopefully you allow me to ask one more question.
After having studied it a bit I am still not sure about the exact working of the bold part:

- EntireTable is just a way to get the entire table in PowerQuery?
- Table.RemoveColumns makes sure the fields are removed for those rows that will not be included? But how does that now interacts with the fact that it is included in the first sort part?

let
    // load your table
    Source = TBL_SOURCE_LL_B2B_FVODA,


    
   // ChangedType = Table.TransformColumnTypes(Source,{{"Asset", type text}, {"Status", type text}, {"Priority", Int64.Type}, {"Timestamp", type date}}),
    
    GrpTable = Table.Group( 
                    Source, 
                    {"AssetId_New"}, 
                    { {
[B]                        "EntireTable", [/B]
[B]                        each Table.Sort( Table.RemoveColumns( _, {"AssetId_New"} ), {"NewColumn.Order", Order.Ascending}){0},[/B]
[B]                        type record} [/B]
                    },
                    GroupKind.Global
    ),
    ExpandedEntireTable = Table.ExpandRecordColumn(GrpTable, "EntireTable", {"Index", "Status", "NewColumn.Order", "Description", "ContractBeginDate", "ContractBeginDate_Proper", "Code", "ContractPeriod", "LeaseAmount"}, {"Index", "Status", "NewColumn.Order", "Description", "ContractBeginDate", "ContractBeginDate_Proper", "Code", "ContractPeriod", "LeaseAmount"})
in
    ExpandedEntireTable

thanks, where this will be very useful!

 

[VBA Geek]

Table.Group performs a grouping of Source by AssetID.
EntireTable is just a name I have given to the new column that is created to host the content of Source grouped by Asset.
So the step GrpTable will contain one column with the unique Assets and another column whose each individual cell contains a table only with rows of the corresponding asset. I am sorting this tables in the second column by Priority and taking only first row afterwards, essentially taking only the lowest priority per Asset ID

 

[michiel_soede]

Thanks. Would it also be possible to first sort on {"ContractBeginDate_Proper", Order.Descending} and then on {"NewColumn.Order", Order.Ascending} where simply adding that in between the query does not work.

 

[michiel_soede]

Sorry i forgot to add a comma. For everybody interested my final code now would now be:

let    // load your table
    Source = TBL_SOURCE_LL_B2B_FVODA,


    
   // ChangedType = Table.TransformColumnTypes(Source,{{"Asset", type text}, {"Status", type text}, {"Priority", Int64.Type}, {"Timestamp", type date}}),
    
    GrpTable = Table.Group( 
                    Source, 
                    {"AssetId_New"}, 
                    { {
                        "EntireTable", 
                        each Table.Sort( Table.RemoveColumns( _, {"AssetId_New"} ), {{"NewColumn.Order", Order.Ascending}, {"ContractBeginDate_Proper", Order.Descending}}){0},
                        type record} 
                    },
                    GroupKind.Global
    ),
    ExpandedEntireTable = Table.ExpandRecordColumn(GrpTable, "EntireTable", {"Index", "Status", "NewColumn.Order", "Description", "ContractBeginDate", "ContractBeginDate_Proper", "Code", "ContractPeriod", "LeaseAmount"}, {"Index", "Status", "NewColumn.Order", "Description", "ContractBeginDate", "ContractBeginDate_Proper", "Code", "ContractPeriod", "LeaseAmount"})
in
    ExpandedEntireTable