Forum Discussion
Data transformation with calculation with M
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.
3 Replies
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?
