1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Power Query Excel

Discussion in 'Business Applications' started by thezazman, Aug 1, 2019.

Thread Status:
Not open for further replies.
  1. thezazman

    thezazman Thread Starter

    Joined:
    Jul 31, 2019
    Messages:
    2
    I am trying to Query a folder that has a bunch of sub folders in it. What i want is for each folder to be the name of a column and to have the continence of each folder listed in the rows under it. For the life of me I can not get this to work in a quick manner. I have looked all over the internet to try and find a way to do this but have not been able to get anything to work. I hope that the pictures that I have provided give a good example of what I am doing and where I am getting lost.



    1. Get file from folder one.png



    2. I select the folder with all the sub folders I need
    two.png


    3. Transform the data because I don't want all the extra stuff
    three.png


    4. Removing all the extra stuff I don't want (maybe I should be keeping one?)
    four.png


    5. This is after I have cleaned it up. I split the one that had the file path and removed everything except for the the folder name since this is what I want to use for the name of the column.
    five.png


    6. I am grouping everything with the folder names.
    six.png


    7. By this point I have grouped everything and then transposed it so that it sits the way I want it but none of the data shows and it only says table. This is as close as I can get to how I want it. If I expand the column it ends up multiplying the next column so by the end I have like 10,000 rows and lots of duplicate file names in each row. This is where I am stuck.
    seven.png
     
  2. thezazman

    thezazman Thread Starter

    Joined:
    Jul 31, 2019
    Messages:
    2
    Please Help
     
  3. Charmian

    Charmian

    Joined:
    Mar 6, 2012
    Messages:
    199
    Hi, I have never used this before and you have given me a way to solve something that I'm struggling with currently. But, I think your problem could be that you are trying to use AllRows and that when you click on Table it shows you all the rows you have grouped. What you may want, (what I am using it for) is to count. So instead of allrows on the drop down, use count rows then you get the Folder Path and count. And that's when I did the transpose.
    I hope this assists you. Your knowledge has certainly helped me! I was going to code VBA in my ms access to do this and due to this post, I have been able to do what was required with just a few finds to reduce the rows! Thank you!
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1230871

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice