M Query Divide

[Anonymous]

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
))

[Thejeswar]

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

[Anonymous]

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))

[Thejeswar · Accepted solution]

@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

[Ashish_Mathur]

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.

[Anonymous]

@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?

[Ashish_Mathur]

Hi,

Try this calculated column formula

=divide(Data[Amount],100)