Скачать или смотреть Parsing a Formatted String into Currency with Power Query (MS Excel)

Discover how to effectively parse formatted monetary strings into Currency types in MS Excel using Power Query. Learn practical steps and code snippets for seamless data transformation.
---
This video is based on the question https://stackoverflow.com/q/77503990/ asked by the user 'Hazard' ( https://stackoverflow.com/u/328078/ ) and on the answer https://stackoverflow.com/a/77505538/ provided by the user 'ninMonkey' ( https://stackoverflow.com/u/341744/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, comments, revision history etc. For example, the original title of the Question was: Parsing a formatted string into Currency with Power Query (MS Excel)

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Parsing a Formatted String into Currency with Power Query (MS Excel)

Are you trying to import data from a REST API into your MS Excel sheet, and finding that monetary fields are formatted in a cumbersome way? You’re not alone! Many users encounter the challenge of parsing strings like 123.45|USD or 678.90|EUR into proper Currency types within Excel. Fortunately, Power Query provides a systematic approach to transform these formatted strings effectively.

The Problem at Hand

When working with APIs, you're likely to receive monetary values in a string format that combines both the amount and the currency code, separated by a pipe (|). This creates difficulties when you need to analyze these financial entries in Excel's currency format because Excel typically expects an amount followed by a currency symbol rather than a code.

Example Strings:

123.45|USD

678.90|EUR

These formats make it tricky to directly convert and utilize the data for financial calculations or proper display within Excel.

The Solution

To address this issue, we can use Power Query to build a query that effectively splits these strings into usable components: the amount and the currency unit. Here’s a detailed step-by-step guide to doing just that.

Step 1: Create a New Query

Open your Excel sheet.

Go to the Data tab.

Choose Get Data then From Other Sources and select Blank Query.

Step 2: Access the Advanced Editor

Now that you have a blank query open:

Click on Advanced Editor.

Replace any existing code with the following script.

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Explaining the Code

Here's a breakdown of what the code accomplishes:

Data Input:

It begins by defining a JSON structure that serves as the source of data containing monetary strings.

Currency Conversion Function:

The AsCurrency function takes a text string (like 678.90|EUR), splits it by the pipe character, and transforms the amount into a Currency type while keeping track of the currency unit.

The optional Culture parameter allows for the specification of different number formats, such as German de-de.

Adding the Column:

Using Table.AddColumn, it appends a new column that contains the transformed values, keeping the original text intact.

Expanding Records:

It then expands the new column to separate the currency unit from the amount into distinct columns.

Changing Types:

Finally, it enforces the correct Excel data types: text for the currency code and Currency.Type for the monetary amount.

Step 4: Finalizing the Query

After pasting the above code, close the editor. Now, your query should process and display the monetary data in a clean, usable format.

Conclusion

By following these steps, you can seamlessly convert formatted monetary strings into Excel's Currency types using Power Query. This not only makes your data more manageable but also opens up more functionalities for analysis and reporting within MS Excel.

Feel free to customize and adapt the given script according to your specific API data formats. With Power Query, you have the flexibility to transform your data and gain valuable insights efficiently.