cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

When Importing from API's Percentage Values are Importing as Text

Having trouble importing from two different API's. The first is OECD.org, the second is stlousifed.org. I am able to successfully import data in tables. The problem I am having is that values coming from the API's for percentages are imported as "text" by Power Bi - Power Query Editor. 

 

For example 4.2 imports as text. The values need to be stored as 4.2%. The problem I am having is trying to transform them. If I select Transform - Data Type - Percentage Power Query Editor and Power Bi convert 4.2 (text) to 420.00%, which is not the correct value. As a workaround, I changed the [Value] type Text to Decimal, then I created a third calculated column called Percent = DIVIDE([Value],100) and then I Transform it to %. For a short term fix, this works kinda. Long term, as I intend to connect to numerous API's that provide the same values it will make the import tedious, not to mention storing extra unnecessary columns. 

 

Any suggestions on how to resolve this using Advanced Editor functions that do not include creating calculated columns would be much appreciated. Below is some example date. Note: Each table to the left, save the "Formulas", one is an API call. Every one of them has a percentage number is stored as "text" during import. 

 

Civilian Unemployment.PNGProducer Price Index Example.PNG

 

 

 

 

 

0 REPLIES 0

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors