Forum Discussion

paw1's avatar
paw1
Helper I
3 years ago
Solved

Flag derive help

Hi 

Can any one please help me to derive the flag based on this logic. If assocaited number value  is not in number field value then its child else parent.

 

numberassociated numberflag
60040child
 390parent
39040child
 600parent
 800child
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjMwUNJRMjFQitWJVlIAMo0tIWwQjSIBUgljW4DYsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [number = _t, #"associated number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"number", Int64.Type}, {"associated number", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"number"}),
    #"Added Custom" = Table.AddColumn(#"Filled Down", "Flag", each if List.Contains(#"Filled Down"[number],[associated number]) then "Parent" else "Child")
in
    #"Added Custom"

    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".

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjMwUNJRMjFQitWJVlIAMo0tIWwQjSIBUgljW4DYsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [number = _t, #"associated number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"number", Int64.Type}, {"associated number", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"number"}),
    #"Added Custom" = Table.AddColumn(#"Filled Down", "Flag", each if List.Contains(#"Filled Down"[number],[associated number]) then "Parent" else "Child")
in
    #"Added Custom"

    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".