+ Reply to Thread
Results 1 to 11 of 11

Power Query - update particular columns

  1. #1
    Forum Contributor
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    365
    Posts
    204

    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 Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    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
    Forum Contributor
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    365
    Posts
    204

    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 Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    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
    Forum Contributor
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    365
    Posts
    204

    Re: Power Query - update particular columns

    hi,

    file attached.

    I hope this will work
    Attached Files Attached Files

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

    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
    Forum Contributor
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    365
    Posts
    204

    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 Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

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

    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
    Forum Contributor
    Join Date
    09-13-2010
    Location
    Poland
    MS-Off Ver
    365
    Posts
    204

    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 Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    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)

Similar Threads

  1. Select sheet after Power Query Update
    By jonrack in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-05-2019, 02:16 PM
  2. Custom Permutations Column in Power Query from two columns in the same Query
    By PaintPaddy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2019, 02:48 PM
  3. [SOLVED] Power Query ranking multiple columns
    By rs1aj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-26-2019, 11:43 PM
  4. Update pivot tables without refreshing power query
    By Acceptablerisk in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-25-2019, 04:48 AM
  5. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  6. Power Query Won't Update
    By Justair07 in forum Excel General
    Replies: 9
    Last Post: 09-29-2016, 04:46 PM
  7. automate power query update from new tab (office excel 2016)
    By ajnuna in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-18-2016, 07:27 AM

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