+ Reply to Thread
Results 1 to 14 of 14

Appending Charts using Power Query Excel 2016

  1. #1
    Registered User
    Join Date
    06-28-2018
    Location
    Germany
    MS-Off Ver
    2016
    Posts
    8

    Red face Appending Charts using Power Query Excel 2016

    Hi every body,

    I am hung up on chart formatting and need your help please.

    I have several tables (same format ) from different projects that I should update monthly to further analyze.

    At the same time, I have to do dashboard to compare all the projects and do an overall analysis on all.

    I want to create a main table that should automatically bring the data from other tables when I update them.

    I use power query to append the tables together, but by saving the table, I'm getting an erorr.

    or if there is any other ways to bring all data from Tables to main Table.

    My table Header is as follows:

    Date. Project name, , Creditors, . const type Nr. Amount, quantity, description,

    2.2.2018 , Same names , different , different .., .... , ...., ... .......



    but in Main table the Projekt column ist with several names.



    Thanks for your ideas in advance

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Appending Charts using Power Query Excel 2016

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Registered User
    Join Date
    06-28-2018
    Location
    Germany
    MS-Off Ver
    2016
    Posts
    8

    Re: Appending Charts using Power Query Excel 2016

    Hi sandy
    Thanks for your reply.

    i have attached the simple file..
    i need to get Main table updated, whenever i update other tables.
    Attached Files Attached Files

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Appending Charts using Power Query Excel 2016

    with PowerQuery (Get&Transform) and PivotTable.
    is that what you want?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-28-2018
    Location
    Germany
    MS-Off Ver
    2016
    Posts
    8

    Re: Appending Charts using Power Query Excel 2016

    yes thats what i need.
    Thanks
    but how you made it?

    I am using Excel 2016 German version and i dont know how to create such a list.
    I have treid using APPEND command, but i get errors.

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Appending Charts using Power Query Excel 2016

    It was a proper way with Append
    what kind of error you got? (translate to english )
    steps:
    • table doesn't need blank rows (as you can see in my example)
    • add tables to PowerQuery
    • Append frist table to the second or vice versa (not merege but append)
    • create PivotTable from Query Append

    I rounded Amount to 2 decimals only
    Last edited by sandy666; 06-29-2018 at 08:59 AM.

  7. #7
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Appending Charts using Power Query Excel 2016

    General Note: Read me

    Try to avoid joining to my Black List by doing this below

    If that takes care of your original question, & to say Thanks and for better Motivation, please
    1. click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
      then
    2. select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
    If you did it already - ignore it.
    Thank you.

  8. #8
    Registered User
    Join Date
    06-28-2018
    Location
    Germany
    MS-Off Ver
    2016
    Posts
    8

    Re: Appending Charts using Power Query Excel 2016

    Hi Dear Sandy,

    I attach the tables to each other, but I get the files doubled.
    but when i am in a Table and Appending the Data to this table, tha date comes correctly in Power table but i get 42 errors, when i click on error it doesnt show me anything in error table.

    i have 8 Tables With 110000 rows.


    I do it as follow.

    -Format Tables.
    - add to power query from Table
    - Close and Load
    - creat links
    same Procedure for all table and at the end.

    Open Power Query editor..

    -Apend tables with each other
    -Save and load
    -Load in a new table.

    but at the end i get either Error or Doubled.

    The Tables are same as your table but somtimes with Zero in Cells.

    what do you think, whats the Problem?

    Thank you so much

  9. #9
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Appending Charts using Power Query Excel 2016

    Hm, what can I say....
    • PQ is case sensitive so word PowerQuery is not the sam as powerquery
    • all data in column should be the same type, btw. null is text, 0 is a number so if 0 is in text column change it to null and vice versa with number column
    • all headers should be different (as you know it from Excel Table)
    • all tables should have the same headers in appropriate columns
    • to Append - will be good if all tables has the same number of columns
    • check query tables for additional spaces on the end and other, non-printable characters (simple TRIM than CLEAN)
    Is there sensitive data in your file? If not, attach this file here (if too big save file as xlsb and try again). If yes - try to desensitize and attach. (whole file!)
    Last edited by sandy666; 07-02-2018 at 05:31 AM.

  10. #10
    Registered User
    Join Date
    06-28-2018
    Location
    Germany
    MS-Off Ver
    2016
    Posts
    8

    Re: Appending Charts using Power Query Excel 2016

    here is the File
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    06-28-2018
    Location
    Germany
    MS-Off Ver
    2016
    Posts
    8

    Re: Appending Charts using Power Query Excel 2016

    Please Update it and see if you get error by updating the file..
    Thanks

  12. #12
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Appending Charts using Power Query Excel 2016

    after clean your source tables I don't see any errors in Append table
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    06-28-2018
    Location
    Germany
    MS-Off Ver
    2016
    Posts
    8

    Re: Appending Charts using Power Query Excel 2016

    I still get this error by updating the Table (reference is not valid , atleast one connection didnt updated).
    but its no problem i am working with your table.

    Thanks alot for your help

  14. #14
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Appending Charts using Power Query Excel 2016

    Clean all your source tables:
    • select each whole sheet
    • select Clear Formats
    • remove all freezes
    • set the same format for all surce table
    • one of your table has additional blank row - remove it
    • set dates to dates (column A) if you want but you can do that in PQ Editor
    • set text format to last column in PQ Editor (mixed text and number)
    • and now you can use Append (probably)

    General Note: Read me

    Try to avoid joining to my Black List by doing this below

    If that takes care of your original question, & to say Thanks and for better Motivation, please
    1. click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
      then
    2. select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
    If you did it already - ignore it.
    Thank you.

+ 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. Replies: 0
    Last Post: 04-05-2018, 01:16 AM
  2. [SOLVED] Power Pivot not showing in excel 2016 one time purchase version
    By sovietchild in forum Excel General
    Replies: 5
    Last Post: 01-22-2018, 09:40 AM
  3. Replies: 7
    Last Post: 07-25-2017, 08:38 AM
  4. Appending Power Query in VBA
    By randallrosa in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-05-2017, 03:48 AM
  5. Update Query Url: Excel 2016 Query
    By igoodable in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-11-2017, 12:45 PM
  6. Replies: 0
    Last Post: 12-01-2016, 05:26 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

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