Forum Discussion

PELNZ_kjm's avatar
PELNZ_kjm
Frequent Visitor
5 years ago
Solved

Prioritising return value according to column

Hi,

 

I'm trying to return a single record per asset/date according to its source, with one source favoured over another (discarded record bolded below)...

 

Raw table:

ASSET        DATE                SOURCE        VALUE        
Asset101/06/2021A10
Asset101/06/2021B15
Asset102/06/2021B12
Asset201/06/2021A23
Asset202/06/2021A20
Asset301/06/2021C50
Asset401/06/2021D70


Desired outcome:

ASSET        DATE                SOURCE        VALUE        
Asset101/06/2021A10
Asset102/06/2021B12
Asset201/06/2021A23
Asset202/06/2021A20
Asset301/06/2021C50
Asset401/06/2021D70

 

I don't need to know the final [SOURCE] in the visualisation, however if source A doesnt have a value for the Asset/Date combination, it should then fallback to source B. There's also a [SOURCE] C & D, however I'm happy to ignore them as only sources A & B overlap in my example.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi PELNZ_kjm 

     

    Here is one way

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwuTi0xVNJRMjDUNzDTNzIwAnEcgdjQQClWB5cCJ5ACUzQFRugKjBAKjLBZYWSMpsAIXQGSG4zRTXAGYlMkBSboClyA2ByoIBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ASSET = _t, DATE = _t, SOURCE = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ASSET", type text}, {"DATE", type date}, {"SOURCE", type text}, {"VALUE", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ASSET", "DATE"}, {{"allrows", each _, type table }}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if List.ContainsAll( [allrows][SOURCE],{"A","B"}) then Table.SelectRows([allrows], each [SOURCE] <>"B") else [allrows]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"ASSET", "DATE", "SOURCE", "VALUE"}, {"ASSET", "DATE", "SOURCE", "VALUE"})
in
    #"Expanded Custom"

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi PELNZ_kjm 

     

    Here is one way

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwuTi0xVNJRMjDUNzDTNzIwAnEcgdjQQClWB5cCJ5ACUzQFRugKjBAKjLBZYWSMpsAIXQGSG4zRTXAGYlMkBSboClyA2ByoIBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ASSET = _t, DATE = _t, SOURCE = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ASSET", type text}, {"DATE", type date}, {"SOURCE", type text}, {"VALUE", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ASSET", "DATE"}, {{"allrows", each _, type table }}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if List.ContainsAll( [allrows][SOURCE],{"A","B"}) then Table.SelectRows([allrows], each [SOURCE] <>"B") else [allrows]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"ASSET", "DATE", "SOURCE", "VALUE"}, {"ASSET", "DATE", "SOURCE", "VALUE"})
in
    #"Expanded Custom"

     

    • PELNZ_kjm's avatar
      PELNZ_kjm
      Frequent Visitor
      5 years ago

      Thanks Vera, I've discovered further issues with visualising due to the data structure, however this achieves exactly what I outlined 🙂