January 7, 2012
Hello, and happy new year.
I know this is probably a very easy thing, but i am struggling with this.
I need to create a custom column that conditionally creates a statement that includes financial information from another field as part of the output.
The logic part is of no issue, but i am having trouble with the Values fields.
The output i am looking to create is something like this: "Please note your account is in Credit to the value of £1,756.96, which is great"
1st it seems i need to change the Values field to a text field else i cannot include it within the text out put
2nd even if i change the Value field to text the output is not formatted as above, and i get something like: "Please note your account is in Credit to the value of 1756.96, which is great"
Is there a way of generating the output as required? And if so is there a way of doing it without having to convert the column to text?
Many thanks, in advance, and sorry if this is a really basic question.
July 16, 2010
Formatting like this isn't usually don't in Power Query as it's not designed to be the final output. It's better done in Power Pivot or Excel where there are functions like FORMAT available to handle this. I'd move this task to Power Pivot if you can.
Alternatively, you could keep the 3 parts of the output i.e.: the first text statement, the amount formatted as currency, and the last text statement, in separate columns and leave them like that, hiding the grid lines so it looks like one sentence. Although it might be tricky to get the spacing correct.