+ Reply to Thread
Results 1 to 2 of 2

Breaking a broad table into multiple sheets to work on some of the columns a time

  1. #1
    Registered User
    Join Date
    07-12-2018
    Location
    London
    MS-Off Ver
    Current
    Posts
    1

    Breaking a broad table into multiple sheets to work on some of the columns a time

    Dear community

    I have an issue I hope you can help with. I am new to using data models.

    So, lets say I have a sheet with a table ("master"). The Master has about 150 rows and I keep on adding columns. Let's say rows are tasks, Columns are for different purposes, like timetracking, who is assigned, very much details. The amount of columns in Master is growing very large, so it is hard to navigate. I tried to use grouping of columns, hiding, but what I really would find more useful is to work with the views I can create from the Power Query editor. E.g.only working on the columns related to time management on one sheet ("time management"), only working on the columns related to assignment on another sheet. Ultimately all the changes made on these sheets should be reflected within each other, such as:
    - a new task (i.e. a new row in Master) is synced to all sheets
    - a change on the "time management" sheet should be reflected in the Master sheet (since it contains all the data).
    Basically it is like breaking a very broad table into multiple pieces on multiple sheets.

    What I have done so far is use the Power Query editor on Master, remove the columns I dont need, and create the sheet ("query")with the selected. I also like the other options that PQ gives me to select what I want to see. However all the changes made on "query" are gone with each refresh of the connection. I guess this is how a Query works - only in one way.

    Is there another way of achieving the purposes I outlined? I might be missing the basics... Still: Thank you in advance.

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Breaking a broad table into multiple sheets to work on some of the columns a time

    PQ works this way: source:Extract => PQ Editor:Transform => result:Load (ETL). If you want to change something do it in source or via PQ Editor. I can suggest PowerPivot for cooperation with PQ.


    but if you really want to change something in the result table (query table) => right click => Table => Convert to Range then change whatever you want
    (but connection betwee the result and Query will be lost)
    Last edited by sandy666; 07-12-2018 at 11:53 AM.

+ 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. Breaking a Pivot into multiple sheets, and then into multiple workbooks (using VBA)
    By gorstrom in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-01-2018, 12:28 PM
  2. Finding work day lead time using networkdays function across multiple sheets
    By artistictiger300 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-25-2017, 12:39 PM
  3. Breaking a Table in to New Sheets
    By agentred in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-21-2014, 10:26 AM
  4. Macro to filter multiple columns of data to multiple work sheets
    By LISSANN in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-10-2014, 07:24 PM
  5. [SOLVED] Plotting multiple lines in Line Chart that have a very broad scale
    By NewTothisStuff in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 08-14-2012, 11:09 AM
  6. Breaking down columns to different sheets
    By tyw in forum Excel General
    Replies: 1
    Last Post: 05-30-2010, 12:15 PM
  7. finding multiple broad terms in columns then pasting entire rows to new sheet
    By thirdcoast in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-01-2010, 03:19 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