'We cannot apply field access to the type Number. Deails: Value = 0, Key = M1'

[HC1801]

Hi,

I don't have much experience with M, and am a new user of Power Query. I've got the above error after running the below code:

#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns1",{{"M1", type number}, {"M2", type number}, {"M3", type number}, {"M4", type number}, {"M5", type number}, {"M6", type number}, {"M7", type number}, {"M8", type number}, {"M9", type number}, {"M10", type number}, {"M11", type number}, {"M12", type number}, {"InvVAT", type number}}),
#"Replaced Value1" = Table.ReplaceValue(#"Changed Type2",null,0,Replacer.ReplaceValue,{"InvVAT"}),
#"Subtract VAT" = Table.TransformColumns(#"Replaced Value1", {
{"M1", each if [M1] <> 0 then [M1] - [InvVAT] else 0},
{"M2", each if [M2] <> 0 then [M2] - [InvVAT] else 0},
{"M3", each if [M3] <> 0 then [M3] - [InvVAT] else 0},
{"M4", each if [M4] <> 0 then [M4] - [InvVAT] else 0},
{"M5", each if [M5] <> 0 then [M5] - [InvVAT] else 0},
{"M6", each if [M6] <> 0 then [M6] - [InvVAT] else 0},
{"M7", each if [M7] <> 0 then [M7] - [InvVAT] else 0},
{"M8", each if [M8] <> 0 then [M8] - [InvVAT] else 0},
{"M9", each if [M9] <> 0 then [M9] - [InvVAT] else 0},
{"M10", each if [M10] <> 0 then [M10] - [InvVAT] else 0},
{"M11", each if [M11] <> 0 then [M11] - [InvVAT] else 0},
{"M12", each if [M12] <> 0 then [M12] - [InvVAT] else 0}
})

The columns M1-M12 & InvVAT all have numerical values. Any nulls for all columns have been converted to 0s, all columns have been converted to Decimal Number type. 

I want it so that each M column is deducted by the InvVAT column if the M column has a value that isn't 0. However, every single cell returns 'Error' instead.

Any help with this would be much appreciated! 

[AlienSx]

Hello, @HC1801 

Table.ReplaceValue(
  #"Replaced Value1",
  each [InvVAT],
  "",
  (v, o, n) => if o <> 0 then v - o else 0,
  {"M1", "M2", "M3", "M4", "M5", "M6", "M7", "M8", "M9", "M10", "M11", "M12"}
)

[JoeBarry · Accepted solution]

Hi @HC1801 

I presume M1, M2 are Month 1 and Month 2 etc???. I can tell you straight off, if you want the resulting visual to mimic a Pivot table, you can't have your data structured like a Pivot table. Highlight all of the M columns and right click and choose unpivot columns. You will will now have  Attribute & Value columns. To have them in order in the visual later, you will need to either convert them to a date if possible or create a number to sort by. 

Highlight the Attribute column then Go to Add Column > Column from examples > selected then in the new column write 1 where M1 is write 2 where m2 is, then the others should Auto Populate. Press ok

Then try something like this when creating the custom column for your calculation. 

if [Value] <> 0 then [Value] - [InvVat] else 0

Load the data into your report. Go to table View highlight the Attribute column and sort by the number column you created.

Create a matrix table. Add the Attribute to the column filed of the Matrix table then add the calculation column you created to the values field and you will get the result you want.

Thanks

Joe

If this post helps, then please Accept it as the solution

[HC1801]

Thanks Joe,

This has worked so marked as solution. I haven't done the last part as I don't really understand what was meant by/the purpose of creating the matrix table. Everything before that is great though - didn't know what you could do with unpivoting columns, so that's really helpful!

Many thanks,

Chris