Forum Discussion

PeterCyos's avatar
PeterCyos
Frequent Visitor
3 years ago

Trying to Get Out Some IDs On the Quarter by a true false Column

SO im trying to Remove from my DDBB some ID if the left the company in the Quarter. But I cant Formulate a new column on Power Query.
This is the model I have:

QuarterLeftID
1falseJohn
1FalseJohn
1TrueJohn
1falseDavid
1falseDavid
1falseDavid
2falsePhil
2truePhil

So What i would Like is that For each ID that in the quarter have a Single True on the column left, its a column that returns for the whole quarter TRUE, Like this:

QuarterLeftIDNew Column
1falseJohnTrue
1FalseJohnTrue
1TrueJohnTrue
1falseDavidFalse
1falseDavidFalse
1falseDavidFalse
2falsePhilTrue
2truePhilTrue
Need Help

1 Reply

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpLzClOBdJe+Rl5SrE6EEE3bIIhRaUYYjDdLollmSkkixohiQZkZObABUsgVkHEYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quarter = _t, Left = _t, ID = _t]),
    #"Lowercased Text" = Table.TransformColumns(Source,{{"Left", Text.Lower, type text}}),
    #"Grouped Rows" = Table.Group(#"Lowercased Text", {"ID"}, {{"ML", each List.Max([Left]), type text}, {"Rows", each _, type table [Quarter=nullable text, Left=text, ID=nullable text]}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Quarter", "Left"}, {"Quarter", "Left"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Rows",{"ID", "Quarter", "Left", "ML"})
in
    #"Reordered 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".