Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
))
Solved! Go to 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
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
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |