replace row value in nested table based on condition in power query

[Anonymous]

Could anyone suggest for below query please?

 I have source columns Material, Plant and Flag Status

· Required to build new column in power query nested table

· Logic:

o If Group of material begin with "A" plant

 - Then check list of flag status column

- If any list row having “No Phase out” then for A plant rows, add new column and write “No Phase out” and other then A plant remain same as Flag Status column

- Else same as Flag Status column

o Else Flag Status column

Hope my question understandable.

Thanks you in advance!

[Anonymous]

Hello,

I found that this solution giving complete table row info but I wanted the info as below:

I dont wanted crossed info inside nested table, any other solution for it?

Thank you in advance

[AlienSx]

@Anonymous I don't see you are grouping by Material at all while my code groups data by Material and then applies some logic to get new flag. Show your code please.  

[Anonymous]

This is the code

let

Source = #"Changed Type",
f = (tbl as table) as table =>
[a = List.Contains(tbl[Flag Status], "No Phase out"),
b = Table.AddColumn(tbl, "new flag", (x) => if (Text.Start(x[Plnt], 1) = "A" and a) then "No Phase out" else x[Flag Status])][b],
gr = Table.Group(Source, "Material", {{"all", each f(_)}}),
expand = Table.ExpandTableColumn(gr, "all", {"Plnt", "Flag Status", "new flag"})
in
expand)

[AlienSx]

@Anonymous I can't get what's going on. You removed your images. Based on the last image I saw one may suggest that you're adding new column with my code which is wrong. I replicated your source table and incorporate into the code. So that this code 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclfSUQowBBJ++QoBGYnFqQr5pSVKsTpQGSMQgSHsiF04wARD2A27apCwkyF2YRyqjVGFYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Material = _t, Plnt = _t, #"Flag Status" = _t]),
    f = (tbl as table) as table =>
    [a = List.Contains(tbl[Flag Status], "No Phase out"),
    b = Table.AddColumn(tbl, "new flag", (x) => if (Text.Start(x[Plnt], 1) = "A" and a) then "No Phase out" else x[Flag Status])][b],
    gr = Table.Group(Source, "Material", {{"all", each f(_)}}),
    expand = Table.ExpandTableColumn(gr, "all", {"Plnt", "Flag Status", "new flag"})
in
    expand

 takes this table as a Source 

and transforms it to the table below. It works as expected. 

[Mahesh0016]

let
Source = #"Table Phase",
f = (tbl as table) as table =>
[a = List.Contains(tbl[Flag Status], "No Phase out"),
b = Table.AddColumn(tbl, "new flag", (x) => if (Text.Start(x[Plnt], 1) = "A" and a) then "No Phase out" else x[Flag Status])][b],
gr = Table.Group(Source, "Material", {{"all", each f(_)}}),
expand = Table.ExpandTableColumn(gr, "all", {"Plnt", "Flag Status", "new flag"})
in
expand

@Anonymous I hope this helps you!Thank You!!

[AlienSx · Accepted solution]

let
    // replace your_table with correct reference to your original data
    Source = your_table,
    f = (tbl as table) as table =>
        [a = List.Contains(tbl[Flag Status], "No Phase out"),
        b = Table.AddColumn(tbl, "new flag", (x) => if (Text.Start(x[Plnt], 1) = "A"  and a) then "No Phase out" else x[Flag Status])][b],
    gr = Table.Group(Source, "Material", {{"all", each f(_)}}),
    expand = Table.ExpandTableColumn(gr, "all", {"Plnt", "Flag Status", "new flag"})
in
    expand