Forum Discussion
'We cannot apply field access to the type Number. Deails: Value = 0, Key = M1'
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!
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 0Load 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
3 Replies
- JoeBarry
Solution Sage
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 0Load 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
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
