Struggling with Replace function after importing a PDF

[RayAlgar]

Hello

As a new user of Power BI, I would really appreciate some help when importing a PDF into Power BI. I am importing a very simple table showing Sweden's GDP by year. However, when I import, two issues arise.

1 A comma is not recognised so $478,2 becomes $4782. I have just been using the replace function and changing this one by one, but this would be challenging if there were hundreds of changes to make. Is there a quicker solution? 

2 Several of the data cells are imported as 'Null'  for some reason. I think it may have something to do with the grey shading from the year 2020. This means when I try and use the replace function, all the null cells change to the same value which I obviously do not want. I have been searching for a solution but do not understand the workaround which seems complex to me. Oh, I wish I could just edit an individual cell.

I think the ability to import PDFs is going to be very useful and so I am keen to understand how to tidy up data.

Can someone please explain simply how to edit these null values. 

Thank you

Ray

[edhans · Accepted solution]

Two things:

1. You didn't say how this was coming in to Power Query. If it is two rows of 30 columns (or however many there are) you need to select the first column and unpivot the others. If you can copy and paste the initial import of data here that would be a HUGE help for me.
2. If all of the values like $428,3 come in las $4283, then
   1. convert those to a value - Currency Type, or Fixed Decimal. Don't worry about the $ sign in the model. That is visual only and will be formattable when you load the data. You don't want whole number and you don't need "number" as that allocates 12 decimal places at least.
   2. Click on that column (because you unpivote above), go to the transform ribbon, and divide by 10. That will put the decimal in the right place.
3. For the nulls, if the data is in sequence, you can simply add an index column, starting with the first year and incrementing by 1 (vs starting with 1 and incrementing by 1) then use that as your year. I can make it dynamic for you so it starts by the first year in your data, but I'd need data vs explaining in text how to use List.Max for a previous step.

Links below on posting tables here for us to use. 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[edhans · Accepted solution]

Two things:

1. You didn't say how this was coming in to Power Query. If it is two rows of 30 columns (or however many there are) you need to select the first column and unpivot the others. If you can copy and paste the initial import of data here that would be a HUGE help for me.
2. If all of the values like $428,3 come in las $4283, then
   1. convert those to a value - Currency Type, or Fixed Decimal. Don't worry about the $ sign in the model. That is visual only and will be formattable when you load the data. You don't want whole number and you don't need "number" as that allocates 12 decimal places at least.
   2. Click on that column (because you unpivote above), go to the transform ribbon, and divide by 10. That will put the decimal in the right place.
3. For the nulls, if the data is in sequence, you can simply add an index column, starting with the first year and incrementing by 1 (vs starting with 1 and incrementing by 1) then use that as your year. I can make it dynamic for you so it starts by the first year in your data, but I'd need data vs explaining in text how to use List.Max for a previous step.

Links below on posting tables here for us to use. 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[RayAlgar]

Thanks for getting back to me.

The data was coming in as two columns. Column one as year and column two as GDP.

I am attaching a link to the file but not quite sure whether this is read-only or editable. This is the initial import.

https://www.dropbox.com/s/0s9116cbkc05atv/sweden%20gdp.pbix?dl=0

[v-yingjl · Accepted solution]

Hi @RayAlgar ,

If you want to replace each null value into different specific values for each year, you may try to use if statement to create new custom column and remove the previous column.

= Table.AddColumn(#"Changed Type", "Custom", each if [Column2] <> null then [Column2] 
else if [Column1] = 2015 then 10 else if [Column1] = 2016 then 20 else 0)

It would be complicated when there are too many years.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.