Forum Discussion

AdamPayne's avatar
AdamPayne
New Member
1 year ago
Solved

Extracting cell value and populating a column, but with criteria to change value

Hi, I've tried searching elsewhere but I'm struggling to find a solution that I follow. Hoping someone on here can help.

 

In power query I want to populate a new column with a value in column2 and index 3. The index restarts every time the value in Source.Name changes. 

 

Can someone help me please? Thanks in advance!

 

Date currently looks like this (I've only includedthe relevant columns)

Source.NameColumn2Index
A 1
A 2
AAA3
A 4
A 5
B 1
B 2
BAA3
B 4
B 5
C 1
CCC2
C 3
C 4
C 5

 

I want it to look like:

Source.NameColumn2IndexNEW COLUMN
A 1AA
A 2AA
AAA3AA
A 4AA
A 5AA
B 1AA
B 2AA
BAA3AA
B 4AA
B 5AA
C 1CC
C 2CC
CCC3CC
C 4CC
C 5CC

 

Need Help
  • slorin's avatar
    slorin
    1 year ago

    Hi AdamPayne 

     

    =Table.Combine(
       Table.Group(
          Your_Source,
          {"Source.Name"},
          {{"Data", (x) => 
              Table.AddIndexColumn(
                Table.AddColumn(x, "NEW COLUMN", each Text.Combine(x[Column2])),
                "Index", 1),
              type table}}
       )[Data]
    )

    Stéphane 

6 Replies

  • slorin's avatar
    slorin
    Icon for Super User rankSuper User
    1 year ago

    Hi AdamPayne 

     

    =Table.Combine(
       Table.Group(
          Your_Source,
          {"Source.Name"},
          {{"Data", (x) => 
              Table.AddIndexColumn(
                Table.AddColumn(x, "NEW COLUMN", each Text.Combine(x[Column2])),
                "Index", 1),
              type table}}
       )[Data]
    )

    Stéphane 

    • MCG's avatar
      MCG
      Icon for Helper I rankHelper I
      1 year ago

      how about

      let
      filter=[Source.Name],
      result_table=Table.SelectRows(XXX,each [Source.Name]=filter),
      result=List.Max(result_table[Column2])
      in result

       

      change XXX to your source step

  • SundarRaj's avatar
    SundarRaj
    Icon for Super User rankSuper User
    1 year ago

    Hi AdamPayne , here's another solution you could look at. Thanks

    Here's the code:
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Name", type text}, {"Column2", type text}, {"Index", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Source.Name"}, {{"All", each _, type table [Source.Name=nullable text, Column2=nullable text, Index=nullable number]}}),
    Custom1 = Table.AddColumn ( #"Grouped Rows" , "New Column" , each List.RemoveNulls ( Table.SelectColumns ( _[All] , "Column2" )[Column2] ){0} ),
    #"Removed Columns" = Table.RemoveColumns(Custom1,{"Source.Name"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"Source.Name", "Column2", "Index"}, {"Source.Name", "Column2", "Index"})
    in
    #"Expanded All"

  • v-prasare's avatar
    v-prasare
    Icon for Community Support rankCommunity Support
    1 year ago

    AdamPayne,

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

     

    SundarRaj, slorin Thanks for your prompt response.

     

     

     

    Thanks,

    Prashanth Are

    MS Fabric community support

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

  • v-prasare's avatar
    v-prasare
    Icon for Community Support rankCommunity Support
    1 year ago

    @AdamPayne, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

     

     

    Thanks,

    Prashanth Are

    MS Fabric community support

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

  • v-prasare's avatar
    v-prasare
    Icon for Community Support rankCommunity Support
    1 year ago

    @AdamPayne, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

     

     

     

    Thanks,

    Prashanth Are

    MS Fabric community support

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query