Data transformation with calculation with M

[maclura]

Hi,

I have a connection to a SQL Server Instance importing a over million rows table on which I want to perform a simple manipulation on some data before import, but I don't know if this is possible (or makes sense) with M.

Basically this is the table

Item	Cost
abc	10
abc	7
abc	0
mnl	0
xyz	0
xyz	0
xyz	0

and what I want to do is:

- if "Item" is not duplicated, or all the "Cost" values in the duplication are equal to 0, do nothing (import as they are now).

- if "Item" is DUPLICATED

      - find the MAX "Cost" value in the duplication,

      - and set all the "Cost" zero value occurrencies to the MAX "Cost" value. 

The above table, after importation should look like

Item	Cost
abc	10
abc	7
abc	10
mnl	0
xyz	0
xyz	0
xyz	0

Thank you for any advice.

[Fowmy · Accepted solution]

@maclura 

You need to group and a little custom column to get it. Please check the attached file below my signature.

[Fowmy · Accepted solution]

@maclura 

You need to group and a little custom column to get it. Please check the attached file below my signature.

[maclura]

Thank you @Fowmy 
Your suggested solution does work with me.

I'd never used the Table.ReplaceValue function.

I have just an additional question which is more curiosity than other.

In your code I read this line

= Table.Group(#"Changed Type1", {"Item"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Max", each List.Max([Cost]), type nullable number}, {"All", each _, type table [Item=nullable text, Cost=nullable number]}})

instead, when I recreated the same using the 'Group by' widzard, this is what I read:

= Table.Group(#"Changed Type1", {"Item", "Cost"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Max", each List.Max([Cost]), type nullable number}, {"All", each _, type table [Item=nullable text, Cost=nullable number]}})

why?

[Fowmy · Accepted solution]

@maclura 

Because you selected both item and cost columns before grouping, I selected only item.