+ Reply to Thread
Results 1 to 11 of 11

Power Query - update particular columns

  1. #1
    Registered User
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    93

    Power Query - update particular columns

    hi,

    I need to creat quite complex table with Power Query. That final table has many (up to 40) columns. The problem is that I need to place few extra columns with advance formulas into the final table (after Power Query). Those columns are not next to each other.


    Is there any way to refresh that table without loosing those extra columns with formulas?

    an exaple:
    PQ source column PQ source column extra column, added ater PQ, with formula PQ source column extra column, added ater PQ, with formula PQ source column PQ source column
    Last edited by afgi; 02-13-2020 at 06:05 PM.

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,186

    Re: Power Query - update particular columns

    1. Please update your profile to show the Excel version you are using.

    2. Why are you adding formulas after Power Query? Why not add those within your query? If you don't know how, then perhaps we can help. Attach a sample workbook, showing your query, and the formulas you are adding.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    93

    Re: Power Query - update particular columns

    hi,

    I am using excel 2016.

    The sample file attached. Basically: orange colums are with formula and the rest has to be updated by power query on a regular basis. The number of rows will increase, the number of columns won't.

    I cannot change order. The original file, has much more columns and rows.
    Attached Files Attached Files

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,186

    Re: Power Query - update particular columns

    It should be simple enough to replace those worksheet formulas within your Query. But your attachment has no query in it - how can I edit your query?!

  5. #5
    Registered User
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    93

    Re: Power Query - update particular columns

    hi,

    file attached.

    I hope this will work
    Attached Files Attached Files

  6. #6
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,186

    Re: Power Query - update particular columns

    So add these lines to your query, to replace the worksheet formulas, and reorder the columns:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    93

    Re: Power Query - update particular columns

    wow, thanks.

    is there any easy way of translating excel formulas to PQ, as you did above in that line: #"Added Barrel Description" = Table.AddC......

  8. #8
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,186

    Re: Power Query - update particular columns

    There's no magic translator. Just step through what the formula is actually doing, and replicate the function in Power Query.

  9. #9
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,053

    Re: Power Query - update particular columns

    Please Login or Register  to view this content.
    I like to combine multiple replace with null/blank with Splitter.SplitByAnyDelimiter()
    Ex:
    Please Login or Register  to view this content.
    Edit: Woops, forgot closing parenthesis.
    Last edited by CK76; 02-14-2020 at 11:05 AM.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  10. #10
    Registered User
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    93

    Re: Power Query - update particular columns

    hi ,

    I have one more question. How did you make GaugeText step? Where in PQ can I find it?

  11. #11
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,186

    Re: Power Query - update particular columns

    It's a custom function. You can see the steps it performs in the code.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1