Forum Discussion

WencyREN's avatar
WencyREN
Frequent Visitor
4 years ago
Solved

Flag first occurrence item in Power Query

Dear, I have a sample dataset as shown below, could you please instruct me on how to add a custom column to flag the first occurrence of the product type (as 1, else as 0) in the Power Query environm...
Need Help
  • Jakinta's avatar
    Jakinta
    4 years ago

    This can help.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYqIiEAhY3VIF3fCIe5MorgLieKk2ovVnFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, XXX = _t, XXX.1 = _t, XXX.2 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.PositionOf( Source[Product], [Product]) ),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Flag", each Number.From([Custom]=[Index])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Index"})
in
    #"Removed Columns"

     

     

arnabmit's avatar
arnabmit
Helper I
4 years ago

Hi Anonymous Jakinta 

 

I have a similar query; I am trying to flag the first occurrence of a combination of values from two different columns.

 

Thanks for any guidance!

 

ProductMonthFirst Occurrence
A11
A10
A21
B11
C11
C21
D11
D10
B10
C20
D21
Jakinta's avatar
Jakinta
Solution Sage
4 years ago

Try the following code with replacing the Source step with your query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ToText = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Month", type text}}),
    List = List.Transform( List.Zip ( {ToText[Product], ToText[Month]} ),  Text.Combine),
    #"Added Custom" = Table.AddColumn(ToText, "Custom", each List.PositionOf( List, [Product]&[Month])),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "First Occurrence", each Number.From( [Custom]=[Index] )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Index"})
in
    #"Removed Columns"

Result