Forum Discussion
M Query Divide
dd
Hello - I have a column coming in from our ERP system that does not properly place the decimal point.
So instead of .5496 the column comes in as a text field and says 54.96. Upon converting to a percent, it then changes to 5496% which of course is not correct.
I think the only way to solve is to create a custom column that divides the original column by 100.
I tried this but it does not work. Any ideas?
= Table.AddColumn(#"Changed Type4", "Custom", each Value.Divide([Discount],100,0
))
Anonymous ,
Guess you got a little confused.
The M-Query should be as follows
= Table.AddColumn(#"Uppercased Text3", "Margin%", each [Margin]/ 100))
where #"Uppercased Text3" is the previous step
Margin% is the new column name
[Margin] is the column that you are going to divide
6 Replies
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", type number}}), #"Inserted Division" = Table.AddColumn(#"Changed Type", "Division", each [Number] / 100, type number) in #"Inserted Division"Hope this helps.
Ashish_Mathur That was fast! I have no doubt this works, but I am total novice with m query. I was just thinking a simple custom column would suffice for this. How would I incorporate this into a custom column?
Hi,
Try this calculated column formula
=divide(Data[Amount],100)
Hi Anonymous ,
Pls. modify the m query as below and use it for getting the new column
#"Add Column" = Table.AddColumn(#"Changed Type4", "Custom", each [Discount]/ 100)
A Custom column screenshot is as below
Sorry, still not quite sure how to code this.
The correct column actually is called Margin (the original column). I was going to create a new one called Margin%. I am getting an error: "the name Add Column" wasn't recognized.
I tried this:
= Table.AddColumn(#"Uppercased Text3", "Margin%", each #"Add Column" = Table.AddColumn(#"Changed Type4", "Margin%", each [Margin]/ 100))
Anonymous ,
Guess you got a little confused.
The M-Query should be as follows
= Table.AddColumn(#"Uppercased Text3", "Margin%", each [Margin]/ 100))
where #"Uppercased Text3" is the previous step
Margin% is the new column name
[Margin] is the column that you are going to divide
