How can I add a custom column to Power BI query to label a product as the current version?

[dekkerge]

I am very new to Power BI but I am building a dashboard of many different products throughout a few different stages of their development which we call "Gates." I'm doing this partly as a learning opportunity so I'm trying to figure out how I can add a column to designate one version of each product as the "Current" version. I want to keep the previous versions in the query as well in order to compare how the products have developed over time.

Below is an example of what the data looks like in the query:

There are a bunch of products, all at different stages of development. I upload a new file to a folder for each product at each gate. I want to add a column to the data set that looks through the Product and Gate columns in the query and labels the most recent version of each product as "Current." So in my example, Prouct 1 is current at gate D, Product 2 is Current at Gate B, Product 3 at C and Product 4 at D. Is there an elegant way to do this in the Query by adding a custom column?

[PwerQueryKees · Accepted solution]

Table.AddColumn(Source, "IsCurrent",
   each [Gate] = List.Max(Table.SelectRows(Source, each (S) => S[Product] = [Product])[Gate]))
   ) 

Not tested, so minor errors may exist....

[Anonymous · Accepted solution]

Hi,

Thanks for the solution PwerQueryKees offered, and i want to offer some more information for user to refer to.

hello @dekkerge , you can use group by in power query.

You can refer to the following code in advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlEwVNJRcgRiQ0MDA6VYHVQJJ5CEgSmmhDNYwtAUQ8IFiC3NUDUYwawwMcCUAFthjCZhDNNhikXCCZtRxjBHmaFJmECNsjTFFId4zxxTAmSSpQWmONh3hkDxWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Gate = _t, Cost = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Gate", type text}, {"Cost", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"CurrentGate", each List.Max([Gate]), type nullable text}, {"Data", each _, type table [Product=nullable text, Gate=nullable text, Cost=nullable number]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Gate", "Cost"}, {"Gate", "Cost"})
in
    #"Expanded Data"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Omid_Motamedise]

Please provide your sample data as table here

[PwerQueryKees · Accepted solution]

Table.AddColumn(Source, "IsCurrent",
   each [Gate] = List.Max(Table.SelectRows(Source, each (S) => S[Product] = [Product])[Gate]))
   ) 

Not tested, so minor errors may exist....