Forum Discussion
Struggling with Replace function after importing a PDF
Two things:
- 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.
- If all of the values like $428,3 come in las $4283, then
- 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.
- 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.
- 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.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.
Community ChampionTwo things:
- 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.
- If all of the values like $428,3 come in las $4283, then
- 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.
- 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.
- 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.
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-yingjlCommunity Support
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.