Forum Discussion
Add an extra field from a data source
- Anonymous
If I didn't misunderstood you, when you refresh in power bi desktop your new added column will automatically be imported.
I had the same issue. My source is a URL that returns a CSV. When the owner of the URL (a vendor) adds a new column to the CSV, PowerBI does NOT detect the new column.
I have gone into Edit queries -> Refresh Preview.
I can copy / paste the URL from the "Source" of the query into a browser and the CSV that returns has the new column.
However, looking in PowerBI...even clicking on the "Source" in the "Applied Steps" so that NOTHING has been done with the source data - no columns hidden, etc - the new column is NOT there.
If I change the URL to nonsense, save, then re-paste the URL into the source it will then find the new columns. A clumsy forced refresh. Should be a better way.
I have this issue also and it is very frustrating. We add addional columns and fields to source csvs all the time.
I have a work around that used to work...until today.
This way you will not lose any of your steps, which is very important especially if you have a complicated report that could break.
The work around is this:
1) refresh everything, make sure the new column is in the source csv
2) under applied steps in query editor, go to source and hit the settings gear icon to right of "source"
3) Choose Open file as "Automatic" hit OK
4) Your table will dissappear, double click the csv file icon, hit "insert" when the insert step box pops up
5) Your newly added column will appear at step 1 "source"
6) to make sure it stays , under "choose columns" or if you have a "removed columns" ...make sure the check box for that new column is checked
7) Your column is good to go now
This usually works. Except today it started giving me a wierd error after applying query edits Which is why I am here searching.
EDIT* I fixed my error. I was being dumb. My work around works again.
What about just changing columns quantity in M query?
I used advanced editor in Data Source:
let Source = Csv.Document(File.Contents("C:\Users\user\Desktop\AddressType.csv"),[Delimiter="|", Columns=5, Encoding=1200, QuoteStyle=QuoteStyle.None]), #"Change1" = Table.TransformColumnTypes(Origen,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}, {"Column4", type datetime}}) in #"Change1"In the code (source line), instead of Columns=5, you just change to Coumns=6 and it adds the new column. It worked for me.
When you modify the Data Source as reneincer suggested, you will not find "Columns=X" in the code when working with an Excel file. I worked around that by simply going into the second line of the code and inserting my new column names in the code line. i.e. - add {"Column2.5", type text} after the "Column2" piece in the code.
Worked perfectly.
Thanks for the suggestion reneincer!
thanks reneincer
that worked amazingly well.... edit query then just 'up' the Columns= by one
perfect
I wish that trick worked for me. It's not though. I can't believe this is even an issue for users. It should not be so complicated to add columns and have them show up that you have to take risks breaking things to add a new bit of data. Boggles the mind how bad that is as a feature in the software. I might look elsewhere for my reporting solution. Did this issue reoccur for you each time and become a big pain over the years, as people ask for changes in the reporting and supply new bits of data? Or did you find a way to keep using PowerBI even though this is a pain? Was it worth the pain? Or would you recommend other more flexible solutions?
hi Terrence
what type of data source do you have?
I have CSV data files. But could save them into loads of other formats,. if that makes any difference...
HI Terrence, I agree completely with you that it should not be this complicated for sure. This is actually my first attempt at creating and modifying my data but it's easy to tell that this will be a problem anytime you import data and need to add new columns. Hopefully in future patches and/or upgrades they'll simplify this process.
Does this work for an Excel file as well?
Same issue. How do show the new column which I just created in the data source (Excel table, new column added but not shown in Power Bi desktop data field)? somehow I cant do those steps suggested above. thanks
Sorry, Pand. the solution I described was the only one i was able to figure out. Let me know if you find another way.
Thanks. That worked like a charm. Much appreciated.
Best,
Alex
Thanks a lot !! Your work around saved my time a lot !!!
Glad it worked and saved time for you. It should really be a lot easier than that in my opinion within Power BI. Hopefully Microsoft will make the change in a future version.
elsalvador182 Thank you for that. I actually couldn't do exactly what you said because my csv file is updated from Sharepoint but the premise helped. When I inserted the step to import CSV, it inserted the whole document with the last column which had fallen off due to the new column being added. Then I could delete the old "import CSV step" and my steps all worked from there on to the end.
It was driving me crazy so thank you for taking the time to add your solution to help someone who needed it...ME!
This is such a good fix. Thank you.
- My data source was a SQL. Same solution. Go the Source Cog and REFRESH PREVIEW on the toolbar. The new columns will appear. Thank God because my build has over one hundred relationship tables.
To add to this. I had a SQL data source. Going into "Edit Queries" and refreshing made the column appear in the preview. However after clicking "Close & Apply" and viewing the fields in the dataset in the right hand panel, the new column still wasn't appearing. I'm not sure if this had something to do with the fact that I had already added some calculated fields.
My solution was to go back into "Edit Queries" and simply move the column from the end to another position in the table. This created a "Reordered Columns" step. After closing and applying now, the new field appeared in the right hand panel. Hope this helps someone.
To add to this thread, I have a sql data source and was having the same issue. I could go into "Edit Query" and refresh the data and the new column would appear in the preview. However when I clicked "Close & Apply" the list of fields in the panel on the right hand side for that data set would not change. It was very frustrating. I didn't want to delete and add the data source because I had already created a bunch of calculated fields that I didn't want to have to do all over again. I'm not sure whether the existence of these extra fields was what was causing the problem.
I decided to try and change the data source in some way to see if that helped. I went back into "Edit Query" and moved the new column into another position. This created a "Reordered Column" step on the query. When I clickec "Close & Apply" this time, the new column appeared in the fields list on the right hand panel. So that was my work around.
