Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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 b...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago
    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
AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    AlienSx
    Icon for Super User rankSuper User
    3 years ago

    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.