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 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!

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

6 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    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
  • Mahesh0016's avatar
    Mahesh0016
    Super User
    3 years ago

    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!!

  • 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)