Dear Ruth! It's a fantastic and fundamental solution that the regular Power Query books dont address and only harp about the Power of the tool but never really demonstrate these important aspects when dealing with dynamically changing data! Brilliant! Kudos to you for addressing this in such a easy way!
Thank you for your help Ruth. It worked perfectly for me. I set up all the renaming steps in a single line. I'll show the previous and the next step for context: #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"RenameCols" = Table.RenameColumns(#"Promoted Headers",{{"F", "FUND"}, {"OF ", "OFFICE"}, {Table.ColumnNames(#"Promoted Headers"){2}, "THIS MONTH"}, {Table.ColumnNames(#"Promoted Headers"){3}, "ACCUMULATED"}}), #"Changed Type" = Table.TransformColumnTypes(#"RenameCols",{{"OFFICE", type text}, {"THIS MONTH", Int64.Type}, {"ACCUMULATED", Int64.Type}}), Best regards.
+David Benaim Hi David, Yeah, same here, it happens very often. The only thing left is to remember to do this when dealing with excel files. New habits are hard to master ;) /Ruth
Ruth, it's 2021, but you can't imagine how happy I am that I found your video and how relevant it is. Was doing a Power BI demo and power query had hard coded headers which were dynamic/changing. Anyhow, failed to account this and during refresh my demo FAILED. Throughout the time I was trying to fix it but it was too late. Anyhow, lesson learned. Thanks a lot for this video.
Hi Ruth! Thanks for this....It really helped solve the issue that I was facing. However my requirement was for multiple columns and here's what i did: DynamicNameHeader = Table.ColumnNames (#"Promoted Headers1") {4}, DynamicNameHeader1 = Table.ColumnNames (#"Promoted Headers1") {5},
DynamicNameHeader2 = Table.ColumnNames (#"Promoted Headers1") {6} Just repeated the steps for the same no of columns that need to be dynamic. This may not be the ideal solution however it does work perfectly when we need to urgently fix it :)
This works! If you don't have promoted header since your excel already contains a table then you can use this: In the advance editor, type line 1 &2 1. DynamicHeaderChange = Table.ColumnNames(Source), 2. DynamicTableFillup = Table.Fillup(#"Change Type", DynamicHeaderChange), then in the formula bar {DynamicHeaderChange{0} - REMEMBER in the row the first row listed as "1" but it should be 0 when changing the formula or if you preferred everything in the advance editor then add line 3. 3. #"Change Type" = Table.TransformColumType(Source, {{DynamicHeaderChange{0}, type date}}),
Cool Approach! Im lazy and typically just catch this before the headers are promoted, Name the numbered default column's to what i want and remove the first row from the table... with that being said I like the idea of capturing and holding elements of the table in dynamic variables :). Thanks again Ruth
Perfect! I needed to grab the name of the last column dynamically, since I grab these data out of Excel Workbook names. I adjusted the code like this: #"Grab The Last Column" = Table.ColumnNames(#"Promoted Headers"){Table.ColumnCount(#"Promoted Headers") - 1}
Grat video Ruth, could you please tell me how to get more than one title changed? Do you have to create the function Table.ColimnNames for each of the titles? Thanks in advance
Congratulations for your vídeos Ruth I follow them closely. They are very practical I need one variation of this application: Imagine that you have to transpose all headers and they changed some names . How do to mention to all headers dinamically w/o going one per one ? Thank you Mil gracias
Thank you a lot for this video. Although it is not completely what I was hoping that it would be. I have a dataset (1pbix files) that I publish to multiple workspaces (one for every client). Every client has (through a parameter) a different database connection. And I have a table of which the first column contains the headers, but of course they are different for every client. And I also do not know them. So in your example you are the one filling in the word "Sales", but I cannot do that as I want the column name to be flexible for every client. Do you have any idea how I could solve something like that?
Hello - This video is great! I am having some issues though addressing multiple column changes. This shows how to update one column, but how do you do it for multiple? Thanks! Melissa
I had the same question and with some experimenting found that all that was needed to do was after the step to promote headers, all that was needed was the line for DynamicHeaderName I will try and show my code here. let Source = Csv.Document(Parameter1,[Delimiter=",", Encoding=1252]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), DynamicNameHeader= Table.ColumnNames(#"Promoted Headers"),
Hi Team, What if we have more than 20-30 columns and we do not know which column would be changed to what, do we have a step/ formula to keep the power query safe and running without error of column name not found or changed etc., thanks in advance.
Hello Ruth, this is a great tip, it helps me a lot. Is there a way for not to chose the number of the header. For instance I have a querie with 64 collum headers that changes daily. Is there a way to do this for all column without putting the number in the function? (DynamicColumHeaders= Table.ColumnNames(#"Promoted Headers"), {0}. for example put {N} and it grabs always the last available or something.
Thank you for this wonderful video. So helpful. If you need to perform this step on multiple columns do you add additional lines or just add more "{ }" references after the "Table.ColumnNames" function?
Did you figure out this one? I am trying this for more than one column but getting an error: DynamicNameHeader=Table.ColumnNames(#"Promoted Headers"){0}, DynamicNameHeader=Table.ColumnNames(#"Promoted Headers"){1}, DynamicNameHeader=Table.ColumnNames(#"Promoted Headers"){2},
Hi there. Thanks for your good sharing. I am newbie to PowerBI. For my project, I would like to rename multiple columns. How should I code it? (especially that DynamicNameHeader syntax in Advanced Editor ... thanks
Hi Ruth, great video! But usually we load so many columns and in some cases f.e. with update the system to HANA many column names were chaged at once, how to fix that? when you have not only one, but many, many columns, basically for all of them? Many thanks!
So glad I found this. However, I have a question, what if I have multiple columns that needs to be renamed? How should I handle that? Do I need to add DynamicHeader line for every column that needs to be renamed?
Thanks Ruth. I think I got it to work without putting the column number in the DynamicNameHeader Line but I went ahead and put the column number in the Table.ColumnNames(#"Promoted Headers"){#} for each Column Changed Type and it seems to be working!
Would you please do this same video but showing how to do multiple columns that need to be renamed? I checked the link to the other guy that you modified this from, but he was very hard to follow along with.
Have you seen this? m.ua-cam.com/video/rWi1fAhowZs/v-deo.html Depending on what you are trying to do this might help too: m.ua-cam.com/video/jlogBrcYZwc/v-deo.html Ruth
Such a brilliant solution. I was manually fixing columns before importing. Thank you! Is there a solution for when sheet names change? I have a workbook I import where the sheet name is the current week of. I've been manually updating before import.
I was able to find a solution. Thanks again. The key for getting this to work for me was to not click combine but to add column to get Excel objects and then expand. social.technet.microsoft.com/Forums/lync/en-US/b9e50702-ff16-4639-9d6b-8b3b4635847e/query-on-folder-of-excel-workbooks-with-different-sheet-names?forum=powerquery
thx so much! That's super useful. I wrote a VBA for my PowerQuery already but with the problem on header name because my file will be updated monthly with rolling months in header... You solve my problem.
How can you apply this to multiple columns? I have the past 3 months in a schedule and they change each month... I am not sure how to use your solution to multiple changing columns.
Hi +Tyler Scott, Could you please post this question in the Power BI Community? community.powerbi.com When you get an answer, the solution will be then indexed by google and others can also benefit from it. Thanks! /Ruth
I figured it out...DynamicNameHeader=Table.ColumnNames(#"Promoted Headers1"), then replace which ever column with Table.ColumnNames(#"Promoted Headers1"){195},
Hello Power Query users… I Have a this problem: Every month I export from SAP a lot of reports. I need to consolidate this reports in one big report. First 5 columns always is the same. But the problem is that the other columns every month have a different names and different numbers according how many days and hours there is in the respective month. If the month have a 30 days I have a 720 columns, if 31 - 744 columns. The headers looks like this: 01.10.2020 01:00, 01.10.2020 02:00,… …,01.11.2020 00:00. Is it possible to make a query that can do this… Thanks! 😊
I had same problem when pulling a data with multiple columns having daily headings changing, was one of the most toughest jobs every faced It was the table 2 from this link ("en.wikipedia.org/wiki/COVID-19_pandemic_in_India")), However referred the above video by Curbal & after 2 days of working, got this query, see if this helps, but this is for the table I imported from wiki, nowhere else on net is there a video which gives guidance about importing table whose header keeps on changing everyday & of course for multiple headers. You can try this for TABLE 2 on link given, & it may help you get ideas for your table QUERY MADE to pull data let #"Table 2" = let Source = Web.Page(Web.Contents("en.wikipedia.org/wiki/COVID-19_pandemic_in_India")), Data2 = Source{2}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), (PULLED UP from Down) DynamicNameHeader=Table.ColumnNames(#"Promoted Headers"), (ADDED) #"Changed Type" = Table.TransformColumnTypes(Data2,{{Table.ColumnNames(#"Promoted Headers"){0}, type text}, {Table.ColumnNames(#"Promoted Headers"){1}, type text}, {Table.ColumnNames(#"Promoted Headers"){2}, type text}, {Table.ColumnNames(#"Promoted Headers"){3}, type text}, {Table.ColumnNames(#"Promoted Headers"){4}, type text}, {Table.ColumnNames(#"Promoted Headers"){5}, type text}}) in Data2, (ADDED) #"Removed Columns" = Table.RemoveColumns(#"Table 2",{"Header"}), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"State/Union Territory", type text}, {"Cases", Int64.Type}, {"Deaths", Int64.Type}, {"Recoveries", Int64.Type}, {"Active", Int64.Type}}) in #"Changed Type" (CHANGED TYPE1 edited as CHANGED TYPE due to cyclic error being given)
Hello Ruth! Thanks a lot for the video! it was very helpful and I managed to make the query work after watching the video! One question though, when the column name changes on the excel, I'm now able to import it through the "Advance Editor", however the pie chart that is making use of the column header that changed stops working. How can I solve this?
@curbal I have done a pivot on a month field and after pivot I do a total for each column month. Since month names would change going forward, my total step would fail. I tried using colmnames in the total formula but it only gives the columname as text which cannot be used for calculation. How can I use columname in table. Add columns. Await ur expert advise
I was searching how to do this and so glad to learn it here. Thank you so much Ruth. Unfortunately, the line of code only works for 1 changing column so I had to insert the code 3x for 3 columns I need to make it work. #"Renamed Columns2" = Table.RenameColumns(#"Split Column by Delimiter", { Table.ColumnNames(#"Split Column by Delimiter") {1}, "Date" } ), #"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns2", { Table.ColumnNames(#"Renamed Columns2") {4}, "Units per Base" } ), #"Renamed Columns" = Table.RenameColumns(#"Renamed Columns1", { Table.ColumnNames(#"Renamed Columns1") {5}, "Base per Unit" } ), I tried to put 3 columns together in a single line of code but it just wouldn't have it and throw out an error below. expression.Error: We expected a RenameOperations value
Muy bueno el vídeo Ruth! Para complicarlo un poco más , si dejáramos la fecha 2017-01-01 y quisiéramos transformarla a número de del año, en este ejemplo el título de la comuna quedaría week 1 o tanto solo 1 Sabes si se podría hacer automáticamente para actualizar con cada pronóstico semanal?
Brillant. What happens however if you add that dynamic column name to the report, and then hit refresh. The report no longer knows what the column name was and errors. Does anyone know a way around this?
Thanks for this video, but if I just want to change the header, I don't it to be dynamic, can I just change the header by default in the advance editor? But i did and it works
In case column order is fixed in the data source table, easier way may be to rename column headers (before promoting headers) and apply filter on date column to remove rows with "Date" as value. This option may be good for people who would like to avoid complex functions.
Nice trick you've shared Ruth, thank you! I'm reading many files from a folder, in theory, all should have the same headers, but... sometimes appear one or two having one column less, for example, having files with this four columns header: Id, Name, Age, Country, sometimes I get a file with headers: Id, Name, Country... so do you know if there is a way to insert that third column header, and put data in blank or zero? :)
Hi, Ruth. I am facing a challenge by dealing with headers. I do a report every month that the data source is an Excel Table. There is a column named "YTD oct" or "YTD nov" (of course, it deppends on the month we actually are). But this trick you just taught, didn't work for me. Do you have any idea how to solve this problem? Thanks a lot for sharing your videos
Hi, you need to unpivot the column to remove the month name without power query complaining. If you need step by step help, please post in the power bi community with details on how your table looks like. /Ruth
I have a autogenerated user function which has column named CustCode. This function is called by a caller. Later when i change the CustCode to CustomerCode, the caller fails as field name in function is different than caller. I couldn't find the way to fix this issue. Please guide.
What seems to be working is...create a new function from sample code that is auto generated by power NI...and then caller will call this new function. However, still i haven't got the remedy to refresh the metadata when an existing function changes...and the caller, even after modifying the code, doesn't work with changed function definition. Please let me know how to do it.
Cool... Thanks for all your effort... I feel, people generally dont change the header column, but add just data below.... i guess that's why microsoft would not have addressed the issue. Anyways ... With such errors.. it is making us to learn M language as well... :D
+RRR program Hi! I read a while back that excel was the most used connector or source for power Bi. The risk for changes in the header names is quite big there and I have seen it happen many times... Another common scenario is changes in the sql queries made specially for Power BI. What is your most common data source? Just curious... /Ruth
for me its 100% excel :D.... but data base is SAP HANA.. i export data from SAP HANA to excel and then use it for analysis... I don't know how to directly access SAP HANA from power BI or power query .. and i have to learn it ... but as of now, i need to get good grip on DAX formulas... And your tutorials are helping in a good way.. thanks
Hello there, How would you solve a situation where you have the exact same report every week but part of the headers will change (a moving week in this case) with every new file. For instance, 1 file will show data from week 201901 until 201952 but the following week will go from 201902 till 202001. The thing is that I would like to keep all the columns, the ones "moving out" and the ones "moving in". The error I get is that it won't find in the newer file the week that "disappear" hence it doesn't work. I am not sure if that's clear or not but I would really appreciate some help here. Currently the only solution I have found is to modify the Excel files which is time consuming and not very friendly user for people who wants to use the tool. Thanks.
Hi Ruth! Great Video. In my case, using excel, PowerQuery generate this code: let Source = Excel.CurrentWorkbook(){[Name="fSales"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendas 2017-01-01", Int64.Type}, {"Data", type datetime}}) in #"Changed Type" How would I manage that? Thanks as always.
Hi Fabio, It will still work. Here is your code: let Source = Excel.CurrentWorkbook(){[Name="fSales"]}[Content], Name=Table.ColumnNames(Source){0}, #"Changed Type" = Table.TransformColumnTypes(Source,{{Table.ColumnNames(Source){0}, {"Data", type datetime}}) in #"Changed Type" /Ruth
This is good stuff, but I did a few tests, Let's say I already have the dynamic code, suppose I rename the header in Power BI to be A. If someone else changes the header to be B in Excel and when I refresh the Power BI report the new header that is renamed in Excel will be updated in Power BI. This actually can be tricky, because I will not be aware of header changes. Also, what if the column is not in the same spot and got move to the right side. How can we make that dynamic?
In order not to break the Visual - Add another step - Renamed Columns header using the same code (= Table.ColumnNames(#"Promoted Headers"){0} and fixed it as a permanent header title. In this way it does not matter what the column name will be called!
Can we make the renamed column headers also dynamic What I mean is make the Sales header( a column is being renamed to Sales) dynamic We are here fixing one the parameters in table.renamecolumns Can we make both the parameters dynamic and make that work in a card view or table view of power BI It would be very helpful if you can provide a way.
This video solved my problem, I understood how Table.ColumnNames work, as a list of the headers. Just to add what was implicity in your video, but wasn't show: You can make a variable store that list and after that u can use that variable to pick the header. In the video example the variable was "DynamicHeaderChange". Instead of keep using the formula Table.ColumnNames, just use the variable "DynamicHeaderChange{0}" so it will pass the first head name. If u want to target all the headers (using a fuction like fill up, just remove the {} and u are good to go, example: Table.FillUp(#"laststep", DynamicHeaderChange)
Hi Ruth, Great Video and I subscribed Immediately . However I was trying to use one of the public API on web at " secure.mas.gov.sg/api/APIDescPage.aspx?resource_id=95932927-c8bc-4e7a-b484-68a66a24edfe " and I am trying to change Periods as all APi have first column different Like End_of_day, End_of_Week -End _Of_Month etc. So wanted to use this solution but the moment I change the API I am unable to make it dynamic. Looks like source get change as all API are different but field name remain same.So can you suggest something?
Hi Manoj, Great, hope you find the content here useful for you! Regarding your question, the best way to get support is the power bi community. Post your question there and make sure you give as much details as possible to get help! /Ruth
Dear Ruth! It's a fantastic and fundamental solution that the regular Power Query books dont address and only harp about the Power of the tool but never really demonstrate these important aspects when dealing with dynamically changing data!
Brilliant! Kudos to you for addressing this in such a easy way!
One more time you solved my problem Ruth!. Your videos are gold! Thank you again! Gracias totales!
Un placer!!
Ruth, Thank you so much for sharing all your amazing tips and tricks. All of your videos are priceless and really do help. Take care and stay safe.
Have a great weekend too Joanne!
Thank you for your help Ruth. It worked perfectly for me. I set up all the renaming steps in a single line. I'll show the previous and the next step for context:
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"RenameCols" = Table.RenameColumns(#"Promoted Headers",{{"F", "FUND"}, {"OF ", "OFFICE"}, {Table.ColumnNames(#"Promoted Headers"){2}, "THIS MONTH"}, {Table.ColumnNames(#"Promoted Headers"){3}, "ACCUMULATED"}}),
#"Changed Type" = Table.TransformColumnTypes(#"RenameCols",{{"OFFICE", type text}, {"THIS MONTH", Int64.Type}, {"ACCUMULATED", Int64.Type}}),
Best regards.
Love this trick. Changed column names account for about 80% of the reasons that my queries break!
+David Benaim Hi David,
Yeah, same here, it happens very often. The only thing left is to remember to do this when dealing with excel files. New habits are hard to master ;)
/Ruth
Ruth, it's 2021, but you can't imagine how happy I am that I found your video and how relevant it is. Was doing a Power BI demo and power query had hard coded headers which were dynamic/changing. Anyhow, failed to account this and during refresh my demo FAILED. Throughout the time I was trying to fix it but it was too late. Anyhow, lesson learned. Thanks a lot for this video.
Yes, power query has not changed that much so most of the stuff I have made is still actual despite the time passed :)
@@CurbalEN still helps a lot in 2022!! Gracias guapa:)
Thanks for the Table.ColumnsNames Dynamic Fun : )
+ExcelIsFun and to Lars for sharing the trick with us :)
/Ruth
Love it when I see other channels I follow comment on videos I watched. No slight to Curbal, but I checked ExcelIsFun for a solution initially.
Hope you got a solution on the video and welcome to the channel :)
/Ruth
Worked beautifully for me, yes, thank you, Ruth!
Fantastic!! Thanks :)
/Ruth
This 6 year old video just helped me today , Thank you
Hi Ruth, Tried this and it worked magic for me!!! love you!!!!!
Yey!! 🥳🥳
I'm watching your VDO from Thailand. Thank you so much
Hi Ruth! Thanks for this....It really helped solve the issue that I was facing. However my requirement was for multiple columns and here's what i did:
DynamicNameHeader = Table.ColumnNames (#"Promoted Headers1") {4},
DynamicNameHeader1 = Table.ColumnNames (#"Promoted Headers1") {5},
DynamicNameHeader2 = Table.ColumnNames (#"Promoted Headers1") {6}
Just repeated the steps for the same no of columns that need to be dynamic.
This may not be the ideal solution however it does work perfectly when we need to urgently fix it :)
This works! If you don't have promoted header since your excel already contains a table then you can use this: In the advance editor, type line 1 &2
1. DynamicHeaderChange = Table.ColumnNames(Source),
2. DynamicTableFillup = Table.Fillup(#"Change Type", DynamicHeaderChange),
then in the formula bar {DynamicHeaderChange{0} - REMEMBER in the row the first row listed as "1" but it should be 0 when changing the formula or if you preferred everything in the advance editor then add line 3.
3. #"Change Type" = Table.TransformColumType(Source, {{DynamicHeaderChange{0}, type date}}),
Thanks for this Ruth - just what I needed. Great work on giving due credit to Lars also
Great!
Beautiful explanation .. Got helped me with my assignment. Got the actual thing was looking for and found no where else on the web. Thanks a lot.
Glad to hear it helped you ;)
/Ruth
Wow this is great and so useful Ruth ! Changing column names has caused me a lot of problems!!!
+Kenny McMillan Yeah, me too! No problems anymore ;)
/Ruth
2017 and Ruth still EPIC as always, thanks my lady!
😂 Thanks!
This is a fantastic solution to a frustrating issue. Thanks!
Cool Approach!
Im lazy and typically just catch this before the headers are promoted, Name the numbered default column's to what i want and remove the first row from the table...
with that being said I like the idea of capturing and holding elements of the table in dynamic variables :).
Thanks again Ruth
+TheDataMinersUnion This time you need to thank Lars, he came up with the trick :)
/Ruth
+TheDataMinersUnion By the way, you have a question on one of your videos, would you like to answer it yourself?
/Ruth
which video? sure I'll take it
+TheDataMinersUnion Manage headers in bulk :)
/Ruth
Muy buena explicación, bastante útil, Ruth Muchas gracias siempre!!
+Norberto Vera Reatiga y muchas gracias por todo tu apoyo! :)
/Ruth
thank you so much, exactly i face this issue and found this video on right time.thank you once again.
Really glad the videos are useful :) and thanks for the feedback!
/Ruth
Wow, I was looking for a solution like this one, which, finally, is very simple :)
Thank you Ruth
+Hossein SATOUR Hi Hossein,
It is so simple that is beautiful, isn't it?
/Ruth
Saved me a lot of time - THANK YOU!
Oh! Perfect! Mission accomplished then ;)
/Ruth
Perfect! I needed to grab the name of the last column dynamically, since I grab these data out of Excel Workbook names. I adjusted the code like this:
#"Grab The Last Column" = Table.ColumnNames(#"Promoted Headers"){Table.ColumnCount(#"Promoted Headers") - 1}
Wonderful, thanks for sharing:)
/Ruth
Brilliant!! It will save me a lot of steps :) Thanks Ruth
Hola Euth. Muy buen dato, guardado en mis favoritos. Saludos
+djeritorres Gracias! :)
/Ruth
Thanks for your video! Really helpful!
This is amazing..........Thanks a bunch ☺
Glad it was useful!!
/Ruth
Great video - this was exactly what I needed!
Perfect!
Thanks to you and Lars☺️👍
Fantastic tip! Thank you!
Great! Thanks to you and Lars!
+Sandip Mukherjee I will answer on his behalf: our pleasure! And thanks for watching Sandip :)
/Ruth
Awesome trick. Many thanks.
Thanks so much, great tip 👍🏽
Grat video Ruth, could you please tell me how to get more than one title changed? Do you have to create the function Table.ColimnNames for each of the titles? Thanks in advance
Thanks, this really helped me today!
Congratulations for your vídeos Ruth
I follow them closely. They are very practical
I need one variation of this application:
Imagine that you have to transpose all headers and they changed some names .
How do to mention to all headers dinamically w/o going one per one ?
Thank you
Mil gracias
Thank you so much. Really helped a lot !
A very neat tip.
+Cloud Hound Glad you liked it and thanks for all the shares :)
/Ruth
Really Good. Thank you.
Thank you a lot for this video. Although it is not completely what I was hoping that it would be. I have a dataset (1pbix files) that I publish to multiple workspaces (one for every client). Every client has (through a parameter) a different database connection. And I have a table of which the first column contains the headers, but of course they are different for every client. And I also do not know them. So in your example you are the one filling in the word "Sales", but I cannot do that as I want the column name to be flexible for every client. Do you have any idea how I could solve something like that?
You saved my life!!!
Yey!!
thank you so much!! god bless you
You welcome, glad it was useful!
/Ruth
Hello - This video is great! I am having some issues though addressing multiple column changes. This shows how to update one column, but how do you do it for multiple?
Thanks!
Melissa
Even I wanted to know the solution for multiple columns.
Have you guys seen this ?
m.ua-cam.com/video/rWi1fAhowZs/v-deo.html
/Ruth
I had the same question and with some experimenting found that all that was needed to do was after the step to promote headers, all that was needed was the line for DynamicHeaderName I will try and show my code here. let
Source = Csv.Document(Parameter1,[Delimiter=",", Encoding=1252]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
DynamicNameHeader= Table.ColumnNames(#"Promoted Headers"),
Hi Team,
What if we have more than 20-30 columns and we do not know which column would be changed to what, do we have a step/ formula to keep the power query safe and running without error of column name not found or changed etc., thanks in advance.
Hello Ruth, this is a great tip, it helps me a lot. Is there a way for not to chose the number of the header. For instance I have a querie with 64 collum headers that changes daily. Is there a way to do this for all column without putting the number in the function? (DynamicColumHeaders= Table.ColumnNames(#"Promoted Headers"), {0}. for example put {N} and it grabs always the last available or something.
God your good. You make everything so simple. Thank you!
😊 Thanks!!
/Ruth
Thank you for this wonderful video. So helpful. If you need to perform this step on multiple columns do you add additional lines or just add more "{ }" references after the "Table.ColumnNames" function?
See my recent reply.
Did you figure out this one? I am trying this for more than one column but getting an error:
DynamicNameHeader=Table.ColumnNames(#"Promoted Headers"){0},
DynamicNameHeader=Table.ColumnNames(#"Promoted Headers"){1},
DynamicNameHeader=Table.ColumnNames(#"Promoted Headers"){2},
Helped me :)) Thank you.
Thank You Very Much!
+rajan77 You welcome rajan!
/Ruth
Hi there. Thanks for your good sharing. I am newbie to PowerBI. For my project, I would like to rename multiple columns. How should I code it? (especially that DynamicNameHeader syntax in Advanced Editor ... thanks
Very nice! Solved my Problem
Great!
/Ruth
Hi Ruth, great video! But usually we load so many columns and in some cases f.e. with update the system to HANA many column names were chaged at once, how to fix that? when you have not only one, but many, many columns, basically for all of them? Many thanks!
THANK YOU SO MUCH FOR THIS!
You welcome!
/Ruth
So glad I found this. However, I have a question, what if I have multiple columns that needs to be renamed? How should I handle that? Do I need to add DynamicHeader line for every column that needs to be renamed?
+R Adams Hi ,
This video will help you out:
m.ua-cam.com/video/rWi1fAhowZs/v-deo.html
/Ruth
Thanks Ruth. I think I got it to work without putting the column number in the DynamicNameHeader Line but I went ahead and put the column number in the Table.ColumnNames(#"Promoted Headers"){#} for each Column Changed Type and it seems to be working!
+R Adams Great!! That is perfect! :) 👏
/Ruth
the columns out of the Data base in the example are consistent.. Mine change ,so it doesnt apply to what I am looking at.
Thank you so much, this is exactly what I was looking for, is it possible to dynamically rename more than one column in the same file?
Would you please do this same video but showing how to do multiple columns that need to be renamed? I checked the link to the other guy that you modified this from, but he was very hard to follow along with.
Are you just doing a transformation to your column names? If so, look into the function List.Zip.
Have you seen this?
m.ua-cam.com/video/rWi1fAhowZs/v-deo.html
Depending on what you are trying to do this might help too:
m.ua-cam.com/video/jlogBrcYZwc/v-deo.html
Ruth
Thanks for helping out!
/Ruth
Thanks very much!
This is possible only in power query editor. Does the name also changes in the visuals or I have to rebuild the visuals?
Such a brilliant solution. I was manually fixing columns before importing. Thank you!
Is there a solution for when sheet names change? I have a workbook I import where the sheet name is the current week of. I've been manually updating before import.
There are ways to manage that, post in the power bi community to get detailed help!
/Ruth
I was able to find a solution. Thanks again. The key for getting this to work for me was to not click combine but to add column to get Excel objects and then expand.
social.technet.microsoft.com/Forums/lync/en-US/b9e50702-ff16-4639-9d6b-8b3b4635847e/query-on-folder-of-excel-workbooks-with-different-sheet-names?forum=powerquery
Thank you Ruth
thx so much! That's super useful. I wrote a VBA for my PowerQuery already but with the problem on header name because my file will be updated monthly with rolling months in header... You solve my problem.
Fantastic! Glad to hear :)
/Ruth
Hi nice Video if i wanted to change my column's language dynamically based on country how can i do that?
Hi!
Check the comments on this video, it might give you ideas:
ua-cam.com/video/JJryD2dXVVw/v-deo.html
/Ruth
Hi Thanks for Reply I saw that Video it's Awesome,But do we have any permanent solution for translating the language of Column Header Dynamically.
You Could use this trick:
m.ua-cam.com/video/rWi1fAhowZs/v-deo.html
I will make a video about it as soon as I have time.
/Ruth
Thank you so much
How can you apply this to multiple columns? I have the past 3 months in a schedule and they change each month... I am not sure how to use your solution to multiple changing columns.
Hi +Tyler Scott, Could you please post this question in the Power BI Community?
community.powerbi.com
When you get an answer, the solution will be then indexed by google and others can also benefit from it.
Thanks! /Ruth
I figured it out...DynamicNameHeader=Table.ColumnNames(#"Promoted Headers1"),
then replace which ever column with Table.ColumnNames(#"Promoted Headers1"){195},
Great! And thanks for sharing :)
/Ruth
Hello Power Query users… I Have a this problem: Every month I export from SAP a lot of reports. I need to consolidate this reports in one big report. First 5 columns always is the same. But the problem is that the other columns every month have a different names and different numbers according how many days and hours there is in the respective month. If the month have a 30 days I have a 720 columns, if 31 - 744 columns. The headers looks like this: 01.10.2020 01:00, 01.10.2020 02:00,… …,01.11.2020 00:00. Is it possible to make a query that can do this… Thanks! 😊
I had same problem when pulling a data with multiple columns having daily headings changing, was one of the most toughest jobs every faced
It was the table 2 from this link
("en.wikipedia.org/wiki/COVID-19_pandemic_in_India")),
However referred the above video by Curbal & after 2 days of working, got this query, see if this helps, but this is for the table I imported from wiki, nowhere else on net is there a video which gives guidance about importing table whose header keeps on changing everyday & of course for multiple headers. You can try this for TABLE 2 on link given, & it may help you get ideas for your table
QUERY MADE to pull data
let
#"Table 2" = let
Source = Web.Page(Web.Contents("en.wikipedia.org/wiki/COVID-19_pandemic_in_India")),
Data2 = Source{2}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), (PULLED UP from Down)
DynamicNameHeader=Table.ColumnNames(#"Promoted Headers"), (ADDED)
#"Changed Type" = Table.TransformColumnTypes(Data2,{{Table.ColumnNames(#"Promoted Headers"){0}, type text}, {Table.ColumnNames(#"Promoted Headers"){1}, type text}, {Table.ColumnNames(#"Promoted Headers"){2}, type text}, {Table.ColumnNames(#"Promoted Headers"){3}, type text}, {Table.ColumnNames(#"Promoted Headers"){4}, type text}, {Table.ColumnNames(#"Promoted Headers"){5}, type text}})
in Data2, (ADDED)
#"Removed Columns" = Table.RemoveColumns(#"Table 2",{"Header"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"State/Union Territory", type text}, {"Cases", Int64.Type}, {"Deaths", Int64.Type}, {"Recoveries", Int64.Type}, {"Active", Int64.Type}})
in
#"Changed Type"
(CHANGED TYPE1 edited as CHANGED TYPE due to cyclic error being given)
Hi, How do I do this for multiple column names and also how I sum those columns?
Awesome!
+Danilo Chavez Thanks for the feedback Danilo!
/Ruth
Hello Ruth! Thanks a lot for the video! it was very helpful and I managed to make the query work after watching the video!
One question though, when the column name changes on the excel, I'm now able to import it through the "Advance Editor", however the pie chart that is making use of the column header that changed stops working. How can I solve this?
thanks for sharing
@curbal I have done a pivot on a month field and after pivot I do a total for each column month. Since month names would change going forward, my total step would fail. I tried using colmnames in the total formula but it only gives the columname as text which cannot be used for calculation. How can I use columname in table. Add columns. Await ur expert advise
How would you do it if not the column changes but the sheet name when combining workbooks? Powerquery adds a column with the "source" of the data.
I was searching how to do this and so glad to learn it here. Thank you so much Ruth. Unfortunately, the line of code only works for 1 changing column so I had to insert the code 3x for 3 columns I need to make it work.
#"Renamed Columns2" = Table.RenameColumns(#"Split Column by Delimiter", { Table.ColumnNames(#"Split Column by Delimiter") {1}, "Date" } ),
#"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns2", { Table.ColumnNames(#"Renamed Columns2") {4}, "Units per Base" } ),
#"Renamed Columns" = Table.RenameColumns(#"Renamed Columns1", { Table.ColumnNames(#"Renamed Columns1") {5}, "Base per Unit" } ),
I tried to put 3 columns together in a single line of code but it just wouldn't have it and throw out
an error below.
expression.Error: We expected a RenameOperations value
Hi Leena Got any solution for this? because i have 28 columns in excel
@@vijaybodkhe8379 use the index position for this.
Hi Ruth,Thanks for Reply is there any way to change the language of table name dynamically
Thanks
Vanni
Muy bueno el vídeo Ruth! Para complicarlo un poco más , si dejáramos la fecha 2017-01-01 y quisiéramos transformarla a número de del año, en este ejemplo el título de la comuna quedaría week 1 o tanto solo 1
Sabes si se podría hacer automáticamente para actualizar con cada pronóstico semanal?
It should be possible... there are functions that convert date to week no. I need to do part 2 with all your questions!
/Ruth
Thank you! I am looking forward to see if you manage to do that
How can I do this If I'm grabbing data from the URL and the URL is changing every day? any link...
Really amazing trick 👌👌
What if the column is present but the case is different. What should one do? I facing this issue currently.
Brillant. What happens however if you add that dynamic column name to the report, and then hit refresh. The report no longer knows what the column name was and errors. Does anyone know a way around this?
Thanks for this video, but if I just want to change the header, I don't it to be dynamic, can I just change the header by default in the advance editor? But i did and it works
In case column order is fixed in the data source table, easier way may be to rename column headers (before promoting headers) and apply filter on date column to remove rows with "Date" as value. This option may be good for people who would like to avoid complex functions.
Nice trick you've shared Ruth, thank you! I'm reading many files from a folder, in theory, all should have the same headers, but... sometimes appear one or two having one column less, for example, having files with this four columns header: Id, Name, Age, Country, sometimes I get a file with headers: Id, Name, Country... so do you know if there is a way to insert that third column header, and put data in blank or zero? :)
Thank you
Hi, Ruth.
I am facing a challenge by dealing with headers. I do a report every month that the data source is an Excel Table.
There is a column named "YTD oct" or "YTD nov" (of course, it deppends on the month we actually are). But this trick you just taught, didn't work for me. Do you have any idea how to solve this problem?
Thanks a lot for sharing your videos
Hi, you need to unpivot the column to remove the month name without power query complaining.
If you need step by step help, please post in the power bi community with details on how your table looks like.
/Ruth
I have a autogenerated user function which has column named CustCode. This function is called by a caller. Later when i change the CustCode to CustomerCode, the caller fails as field name in function is different than caller. I couldn't find the way to fix this issue. Please guide.
What seems to be working is...create a new function from sample code that is auto generated by power NI...and then caller will call this new function.
However, still i haven't got the remedy to refresh the metadata when an existing function changes...and the caller, even after modifying the code, doesn't work with changed function definition. Please let me know how to do it.
Cool... Thanks for all your effort...
I feel, people generally dont change the header column, but add just data below.... i guess that's why microsoft would not have addressed the issue.
Anyways ... With such errors.. it is making us to learn M language as well... :D
+RRR program Hi!
I read a while back that excel was the most used connector or source for power Bi.
The risk for changes in the header names is quite big there and I have seen it happen many times...
Another common scenario is changes in the sql queries made specially for Power BI.
What is your most common data source? Just curious...
/Ruth
for me its 100% excel :D.... but data base is SAP HANA.. i export data from SAP HANA to excel and then use it for analysis... I don't know how to directly access SAP HANA from power BI or power query .. and i have to learn it ... but as of now, i need to get good grip on DAX formulas...
And your tutorials are helping in a good way.. thanks
+RRR program Great! Happy it is helping :)
/Ruth
anyone know if this will work for querys from web?
Hello there,
How would you solve a situation where you have the exact same report every week but part of the headers will change (a moving week in this case) with every new file. For instance, 1 file will show data from week 201901 until 201952 but the following week will go from 201902 till 202001. The thing is that I would like to keep all the columns, the ones "moving out" and the ones "moving in". The error I get is that it won't find in the newer file the week that "disappear" hence it doesn't work.
I am not sure if that's clear or not but I would really appreciate some help here. Currently the only solution I have found is to modify the Excel files which is time consuming and not very friendly user for people who wants to use the tool.
Thanks.
Hi Ruth! Great Video.
In my case, using excel, PowerQuery generate this code:
let
Source = Excel.CurrentWorkbook(){[Name="fSales"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendas 2017-01-01", Int64.Type}, {"Data", type datetime}})
in
#"Changed Type"
How would I manage that?
Thanks as always.
Hi Fabio,
It will still work. Here is your code:
let
Source = Excel.CurrentWorkbook(){[Name="fSales"]}[Content],
Name=Table.ColumnNames(Source){0},
#"Changed Type" = Table.TransformColumnTypes(Source,{{Table.ColumnNames(Source){0}, {"Data", type datetime}})
in
#"Changed Type"
/Ruth
You are amazing!!! Thank you.
+Fábio Coatis My pleasure!! :)
/Ruth
This is good stuff, but I did a few tests,
Let's say I already have the dynamic code, suppose I rename the header in Power BI to be A. If someone else changes the header to be B in Excel and when I refresh the Power BI report the new header that is renamed in Excel will be updated in Power BI. This actually can be tricky, because I will not be aware of header changes.
Also, what if the column is not in the same spot and got move to the right side. How can we make that dynamic?
In order not to break the Visual - Add another step - Renamed Columns header using the same code (= Table.ColumnNames(#"Promoted Headers"){0} and fixed it as a permanent header title. In this way it does not matter what the column name will be called!
Hi, thanks for amazing solution.
Please tell me how to Sum the dynamic column in power Query
How to do it for multiple columns
Can we make the renamed column headers also dynamic
What I mean is make the Sales header( a column is being renamed to Sales) dynamic
We are here fixing one the parameters in table.renamecolumns
Can we make both the parameters dynamic and make that work in a card view or table view of power BI
It would be very helpful if you can provide a way.
This video solved my problem, I understood how Table.ColumnNames work, as a list of the headers.
Just to add what was implicity in your video, but wasn't show:
You can make a variable store that list and after that u can use that variable to pick the header.
In the video example the variable was "DynamicHeaderChange".
Instead of keep using the formula Table.ColumnNames, just use the variable "DynamicHeaderChange{0}" so it will pass the first head name. If u want to target all the headers (using a fuction like fill up, just remove the {} and u are good to go, example: Table.FillUp(#"laststep", DynamicHeaderChange)
How to pass multiple columns in query
Can you please post your question in the power bi community? Thanks!
/Ruth
Hi Ruth, Great Video and I subscribed Immediately .
However I was trying to use one of the public API on web at " secure.mas.gov.sg/api/APIDescPage.aspx?resource_id=95932927-c8bc-4e7a-b484-68a66a24edfe " and I am trying to change Periods as all APi have first column different Like End_of_day, End_of_Week -End _Of_Month etc. So wanted to use this solution but the moment I change the API I am unable to make it dynamic. Looks like source get change as all API are different but field name remain same.So can you suggest something?
Hi Manoj,
Great, hope you find the content here useful for you!
Regarding your question, the best way to get support is the power bi community. Post your question there and make sure you give as much details as possible to get help!
/Ruth
Surely its easier to remove the premote headers step, remove first row, rename columns
1000+ Likes!!:-)
😊