Managing changing column names in Power Query #10: (M)agic (M)ondays

Поділитися
Вставка
  • Опубліковано 16 січ 2025

КОМЕНТАРІ • 170

  • @darshantalreja1312
    @darshantalreja1312 3 роки тому

    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!

  • @bcippitelli
    @bcippitelli 4 роки тому

    One more time you solved my problem Ruth!. Your videos are gold! Thank you again! Gracias totales!

  • @joanneosborne2428
    @joanneosborne2428 3 роки тому

    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.

    • @CurbalEN
      @CurbalEN  3 роки тому

      Have a great weekend too Joanne!

  • @arielarce3092
    @arielarce3092 3 роки тому +1

    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.

  • @learnspreadsheets
    @learnspreadsheets 7 років тому +2

    Love this trick. Changed column names account for about 80% of the reasons that my queries break!

    • @CurbalEN
      @CurbalEN  7 років тому +1

      +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

  • @anw1986
    @anw1986 3 роки тому

    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.

    • @CurbalEN
      @CurbalEN  3 роки тому +1

      Yes, power query has not changed that much so most of the stuff I have made is still actual despite the time passed :)

    • @Symium
      @Symium 3 роки тому

      @@CurbalEN still helps a lot in 2022!! Gracias guapa:)

  • @excelisfun
    @excelisfun 7 років тому +5

    Thanks for the Table.ColumnsNames Dynamic Fun : )

    • @CurbalEN
      @CurbalEN  7 років тому +1

      +ExcelIsFun and to Lars for sharing the trick with us :)
      /Ruth

    • @davekimball3610
      @davekimball3610 7 років тому

      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.

    • @CurbalEN
      @CurbalEN  7 років тому +1

      Hope you got a solution on the video and welcome to the channel :)
      /Ruth

    • @davekimball3610
      @davekimball3610 7 років тому +1

      Worked beautifully for me, yes, thank you, Ruth!

    • @CurbalEN
      @CurbalEN  7 років тому

      Fantastic!! Thanks :)
      /Ruth

  • @sivaperumal5313
    @sivaperumal5313 Рік тому

    This 6 year old video just helped me today , Thank you

  • @capitalmantra5544
    @capitalmantra5544 4 роки тому

    Hi Ruth, Tried this and it worked magic for me!!! love you!!!!!

  • @WanLazyBear
    @WanLazyBear 3 роки тому

    I'm watching your VDO from Thailand. Thank you so much

  • @Fm1051-l9l
    @Fm1051-l9l 4 роки тому +2

    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 :)

  • @jackeroo75
    @jackeroo75 4 роки тому

    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}}),

  • @martintaylor9356
    @martintaylor9356 3 роки тому

    Thanks for this Ruth - just what I needed. Great work on giving due credit to Lars also

  • @rakeshrao4876
    @rakeshrao4876 5 років тому

    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.

    • @CurbalEN
      @CurbalEN  5 років тому

      Glad to hear it helped you ;)
      /Ruth

  • @kennyquango76
    @kennyquango76 7 років тому +1

    Wow this is great and so useful Ruth ! Changing column names has caused me a lot of problems!!!

    • @CurbalEN
      @CurbalEN  7 років тому

      +Kenny McMillan Yeah, me too! No problems anymore ;)
      /Ruth

  • @samiraslan9735
    @samiraslan9735 Рік тому

    2017 and Ruth still EPIC as always, thanks my lady!

  • @TheCrod2010
    @TheCrod2010 4 роки тому

    This is a fantastic solution to a frustrating issue. Thanks!

  • @thedataminersunion3051
    @thedataminersunion3051 7 років тому +1

    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

    • @CurbalEN
      @CurbalEN  7 років тому

      +TheDataMinersUnion This time you need to thank Lars, he came up with the trick :)
      /Ruth

    • @CurbalEN
      @CurbalEN  7 років тому

      +TheDataMinersUnion By the way, you have a question on one of your videos, would you like to answer it yourself?
      /Ruth

    • @thedataminersunion3051
      @thedataminersunion3051 7 років тому

      which video? sure I'll take it

    • @CurbalEN
      @CurbalEN  7 років тому

      +TheDataMinersUnion Manage headers in bulk :)
      /Ruth

  • @NorbertoVeraReatigaNVR
    @NorbertoVeraReatigaNVR 7 років тому +1

    Muy buena explicación, bastante útil, Ruth Muchas gracias siempre!!

    • @CurbalEN
      @CurbalEN  7 років тому

      +Norberto Vera Reatiga y muchas gracias por todo tu apoyo! :)
      /Ruth

  • @mohamedaboobackersiddique7322
    @mohamedaboobackersiddique7322 7 років тому +1

    thank you so much, exactly i face this issue and found this video on right time.thank you once again.

    • @CurbalEN
      @CurbalEN  7 років тому

      Really glad the videos are useful :) and thanks for the feedback!
      /Ruth

  • @kingparc
    @kingparc 7 років тому +1

    Wow, I was looking for a solution like this one, which, finally, is very simple :)
    Thank you Ruth

    • @CurbalEN
      @CurbalEN  7 років тому +1

      +Hossein SATOUR Hi Hossein,
      It is so simple that is beautiful, isn't it?
      /Ruth

  • @brandondensmore7663
    @brandondensmore7663 6 років тому +1

    Saved me a lot of time - THANK YOU!

    • @CurbalEN
      @CurbalEN  6 років тому

      Oh! Perfect! Mission accomplished then ;)
      /Ruth

  • @bostjanbrumec7174
    @bostjanbrumec7174 5 років тому +5

    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}

    • @CurbalEN
      @CurbalEN  5 років тому

      Wonderful, thanks for sharing:)
      /Ruth

  • @90hsilva
    @90hsilva 4 роки тому

    Brilliant!! It will save me a lot of steps :) Thanks Ruth

  • @djeritorres
    @djeritorres 7 років тому +2

    Hola Euth. Muy buen dato, guardado en mis favoritos. Saludos

    • @CurbalEN
      @CurbalEN  7 років тому +1

      +djeritorres Gracias! :)
      /Ruth

  • @pierrei2889
    @pierrei2889 3 роки тому

    Thanks for your video! Really helpful!

  • @selenevera8834
    @selenevera8834 6 років тому +1

    This is amazing..........Thanks a bunch ☺

    • @CurbalEN
      @CurbalEN  6 років тому

      Glad it was useful!!
      /Ruth

  • @tbdntbd6097
    @tbdntbd6097 4 роки тому

    Great video - this was exactly what I needed!

  • @ashutoshsingh6177
    @ashutoshsingh6177 4 роки тому

    Thanks to you and Lars☺️👍

  • @CAKimberlyLewis
    @CAKimberlyLewis 3 роки тому

    Fantastic tip! Thank you!

  • @sandip_bettereveryday
    @sandip_bettereveryday 7 років тому +1

    Great! Thanks to you and Lars!

    • @CurbalEN
      @CurbalEN  7 років тому

      +Sandip Mukherjee I will answer on his behalf: our pleasure! And thanks for watching Sandip :)
      /Ruth

  • @henrytran4782
    @henrytran4782 4 роки тому

    Awesome trick. Many thanks.

  • @balrajvirdee1087
    @balrajvirdee1087 3 роки тому

    Thanks so much, great tip 👍🏽

  • @mariaalcala5159
    @mariaalcala5159 3 роки тому

    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

  • @stephencross4978
    @stephencross4978 4 роки тому

    Thanks, this really helped me today!

  • @carlosgines1518
    @carlosgines1518 3 роки тому +1

    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

  • @VigneshValentine
    @VigneshValentine 4 роки тому

    Thank you so much. Really helped a lot !

  • @CloudhoundCoUk
    @CloudhoundCoUk 7 років тому +1

    A very neat tip.

    • @CurbalEN
      @CurbalEN  7 років тому

      +Cloud Hound Glad you liked it and thanks for all the shares :)
      /Ruth

  • @GopiNath-fp5ly
    @GopiNath-fp5ly 3 роки тому

    Really Good. Thank you.

  • @jelledaems808
    @jelledaems808 3 роки тому +1

    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?

  • @mastercamer
    @mastercamer 4 роки тому

    You saved my life!!!

  • @SatvikKapoor
    @SatvikKapoor 6 років тому +1

    thank you so much!! god bless you

    • @CurbalEN
      @CurbalEN  6 років тому

      You welcome, glad it was useful!
      /Ruth

  • @melissaj4843
    @melissaj4843 5 років тому +3

    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

    • @robinmgupta5638
      @robinmgupta5638 5 років тому

      Even I wanted to know the solution for multiple columns.

    • @CurbalEN
      @CurbalEN  5 років тому

      Have you guys seen this ?
      m.ua-cam.com/video/rWi1fAhowZs/v-deo.html
      /Ruth

    • @jasonalvarez1132
      @jasonalvarez1132 4 роки тому

      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"),

  • @sanchitmalhotra753
    @sanchitmalhotra753 4 роки тому +1

    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.

  • @paulodacosta8083
    @paulodacosta8083 4 роки тому +1

    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.

  • @REInvestorCEO
    @REInvestorCEO 5 років тому

    God your good. You make everything so simple. Thank you!

    • @CurbalEN
      @CurbalEN  5 років тому

      😊 Thanks!!
      /Ruth

  • @MichaelVB226
    @MichaelVB226 4 роки тому

    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?

    • @jackeroo75
      @jackeroo75 4 роки тому

      See my recent reply.

    • @mariadanielaledezmacastro74
      @mariadanielaledezmacastro74 4 роки тому

      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},

  • @pranjalagrawal4167
    @pranjalagrawal4167 4 роки тому

    Helped me :)) Thank you.

  • @TheDistractionStudio
    @TheDistractionStudio 7 років тому +1

    Thank You Very Much!

    • @CurbalEN
      @CurbalEN  7 років тому +1

      +rajan77 You welcome rajan!
      /Ruth

  • @bennylbkooi
    @bennylbkooi 3 роки тому

    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

  • @sebastianritter9256
    @sebastianritter9256 4 роки тому

    Very nice! Solved my Problem

  • @anjelarieder9924
    @anjelarieder9924 2 роки тому

    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!

  • @damianprzybylski189
    @damianprzybylski189 5 років тому

    THANK YOU SO MUCH FOR THIS!

    • @CurbalEN
      @CurbalEN  5 років тому

      You welcome!
      /Ruth

  • @MultiRAdams
    @MultiRAdams 7 років тому +1

    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?

    • @CurbalEN
      @CurbalEN  7 років тому

      +R Adams Hi ,
      This video will help you out:
      m.ua-cam.com/video/rWi1fAhowZs/v-deo.html
      /Ruth

    • @MultiRAdams
      @MultiRAdams 7 років тому +2

      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!

    • @CurbalEN
      @CurbalEN  7 років тому +1

      +R Adams Great!! That is perfect! :) 👏
      /Ruth

    • @Tscott3334
      @Tscott3334 6 років тому

      the columns out of the Data base in the example are consistent.. Mine change ,so it doesnt apply to what I am looking at.

  • @LOSURFNDANCE
    @LOSURFNDANCE 3 роки тому

    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?

  • @rachelwalden4323
    @rachelwalden4323 6 років тому +1

    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.

    • @brianxyz
      @brianxyz 6 років тому

      Are you just doing a transformation to your column names? If so, look into the function List.Zip.

    • @CurbalEN
      @CurbalEN  6 років тому

      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

    • @CurbalEN
      @CurbalEN  6 років тому

      Thanks for helping out!
      /Ruth

  • @darrenvinales419
    @darrenvinales419 3 роки тому

    Thanks very much!

  • @mayurabnave5684
    @mayurabnave5684 2 роки тому

    This is possible only in power query editor. Does the name also changes in the visuals or I have to rebuild the visuals?

  • @MarshaMinus100
    @MarshaMinus100 4 роки тому

    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.

    • @CurbalEN
      @CurbalEN  4 роки тому +1

      There are ways to manage that, post in the power bi community to get detailed help!
      /Ruth

    • @MarshaMinus100
      @MarshaMinus100 4 роки тому

      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

  • @so1zy
    @so1zy 3 роки тому

    Thank you Ruth

  • @seastar315
    @seastar315 5 років тому

    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.

    • @CurbalEN
      @CurbalEN  5 років тому

      Fantastic! Glad to hear :)
      /Ruth

  • @vannikannankuthalam144
    @vannikannankuthalam144 6 років тому +1

    Hi nice Video if i wanted to change my column's language dynamically based on country how can i do that?

    • @CurbalEN
      @CurbalEN  6 років тому

      Hi!
      Check the comments on this video, it might give you ideas:
      ua-cam.com/video/JJryD2dXVVw/v-deo.html
      /Ruth

    • @vannikannankuthalam144
      @vannikannankuthalam144 6 років тому +1

      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.

    • @CurbalEN
      @CurbalEN  6 років тому

      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

  • @MrBumbum2812
    @MrBumbum2812 4 роки тому

    Thank you so much

  • @Tscott3334
    @Tscott3334 6 років тому +1

    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.

    • @CurbalEN
      @CurbalEN  6 років тому

      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

    • @Tscott3334
      @Tscott3334 6 років тому +1

      I figured it out...DynamicNameHeader=Table.ColumnNames(#"Promoted Headers1"),
      then replace which ever column with Table.ColumnNames(#"Promoted Headers1"){195},

    • @CurbalEN
      @CurbalEN  6 років тому

      Great! And thanks for sharing :)
      /Ruth

  • @allpetrov
    @allpetrov 4 роки тому

    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! 😊

    • @ravishete2001
      @ravishete2001 4 роки тому +1

      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)

  • @harvinderdhami6878
    @harvinderdhami6878 3 роки тому

    Hi, How do I do this for multiple column names and also how I sum those columns?

  • @danilochavez4167
    @danilochavez4167 7 років тому +1

    Awesome!

    • @CurbalEN
      @CurbalEN  7 років тому

      +Danilo Chavez Thanks for the feedback Danilo!
      /Ruth

  • @goncalohorta1769
    @goncalohorta1769 4 роки тому

    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?

  • @FRANKWHITE1996
    @FRANKWHITE1996 2 роки тому

    thanks for sharing

  • @imranpearl
    @imranpearl 4 роки тому

    @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

  • @M1and5M
    @M1and5M 2 роки тому

    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.

  • @lenac3587
    @lenac3587 6 років тому

    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

    • @vijaybodkhe8379
      @vijaybodkhe8379 5 років тому +1

      Hi Leena Got any solution for this? because i have 28 columns in excel

    • @ssunitech6890
      @ssunitech6890 4 роки тому

      @@vijaybodkhe8379 use the index position for this.

  • @vannikannankuthalam144
    @vannikannankuthalam144 6 років тому

    Hi Ruth,Thanks for Reply is there any way to change the language of table name dynamically
    Thanks
    Vanni

  • @OmarAlvarezGarcia
    @OmarAlvarezGarcia 5 років тому

    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?

    • @CurbalEN
      @CurbalEN  5 років тому +1

      It should be possible... there are functions that convert date to week no. I need to do part 2 with all your questions!
      /Ruth

    • @OmarAlvarezGarcia
      @OmarAlvarezGarcia 5 років тому

      Thank you! I am looking forward to see if you manage to do that

  • @MrvladivostokMr
    @MrvladivostokMr 4 роки тому

    How can I do this If I'm grabbing data from the URL and the URL is changing every day? any link...

  • @abhijeetghosh27
    @abhijeetghosh27 4 роки тому

    Really amazing trick 👌👌
    What if the column is present but the case is different. What should one do? I facing this issue currently.

  • @alexkeil7533
    @alexkeil7533 6 років тому

    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?

  • @sefbookeeping
    @sefbookeeping 4 роки тому

    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

  • @VinaykumarPatel
    @VinaykumarPatel 7 років тому

    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.

  • @luisalbertorocham8419
    @luisalbertorocham8419 5 років тому

    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? :)

  • @amani1997
    @amani1997 3 роки тому

    Thank you

  • @thainademenezes3345
    @thainademenezes3345 5 років тому

    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

    • @CurbalEN
      @CurbalEN  5 років тому

      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

  • @pravinjoshi72
    @pravinjoshi72 3 роки тому

    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.

    • @pravinjoshi72
      @pravinjoshi72 3 роки тому

      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.

  • @rrrprogram8667
    @rrrprogram8667 7 років тому +1

    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

    • @CurbalEN
      @CurbalEN  7 років тому

      +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

    • @rrrprogram8667
      @rrrprogram8667 7 років тому +1

      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

    • @CurbalEN
      @CurbalEN  7 років тому

      +RRR program Great! Happy it is helping :)
      /Ruth

  • @thomasmason4241
    @thomasmason4241 6 років тому +1

    anyone know if this will work for querys from web?

  • @Alan.DL7
    @Alan.DL7 5 років тому

    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.

  • @fcoatis
    @fcoatis 7 років тому +1

    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.

    • @CurbalEN
      @CurbalEN  7 років тому

      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

    • @fcoatis
      @fcoatis 7 років тому +1

      You are amazing!!! Thank you.

    • @CurbalEN
      @CurbalEN  7 років тому

      +Fábio Coatis My pleasure!! :)
      /Ruth

  • @jasona7029
    @jasona7029 5 років тому

    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?

  • @nickchua
    @nickchua 2 роки тому

    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!

  • @umeshkjadhav
    @umeshkjadhav 5 років тому

    Hi, thanks for amazing solution.
    Please tell me how to Sum the dynamic column in power Query

  • @vijaybodkhe8379
    @vijaybodkhe8379 5 років тому

    How to do it for multiple columns

  • @dipanjanray3922
    @dipanjanray3922 3 роки тому

    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.

  • @ShaDoWZ0D
    @ShaDoWZ0D 4 роки тому +3

    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)

  • @munna629
    @munna629 5 років тому

    How to pass multiple columns in query

    • @CurbalEN
      @CurbalEN  5 років тому

      Can you please post your question in the power bi community? Thanks!
      /Ruth

  • @saxena1955
    @saxena1955 6 років тому

    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?

    • @CurbalEN
      @CurbalEN  6 років тому

      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

  • @TheColjackoneal
    @TheColjackoneal 4 роки тому

    Surely its easier to remove the premote headers step, remove first row, rename columns

  • @ravishete2001
    @ravishete2001 4 роки тому

    1000+ Likes!!:-)