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

[AdamPayne]

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.Name	Column2	Index
A		1
A		2
A	AA	3
A		4
A		5
B		1
B		2
B	AA	3
B		4
B		5
C		1
C	CC	2
C		3
C		4
C		5

I want it to look like:

Source.Name	Column2	Index	NEW COLUMN
A		1	AA
A		2	AA
A	AA	3	AA
A		4	AA
A		5	AA
B		1	AA
B		2	AA
B	AA	3	AA
B		4	AA
B		5	AA
C		1	CC
C		2	CC
C	CC	3	CC
C		4	CC
C		5	CC

[v-prasare]

@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]

@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]

@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

[SundarRaj]

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"

[slorin · Accepted solution]

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]

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