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

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's avatar
    AlienSx
    Super 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
        Super 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.