Results 1 to 8 of 8

Thread: Is Power Query the right tool for this project?

  1. #1
    Seeker OldCityCat's Avatar
    Join Date
    Apr 2017
    Location
    St Augustine FL
    Posts
    6
    Articles
    0
    Excel Version
    2016

    Is Power Query the right tool for this project?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I download a week report from a website ,just like a lot of IT employees.
    As you can guess it a Report formatted a text file with on consistent delimiters.
    I have tried using power query but without consistent delimiters I didn't get very far.
    So I turned to VBA a was able remove Report Headers, Page Headers, and empty rows.

    I have attached a small sample of the results,
    I would like to know if PQ can break column A into one record per row.
    Once I have that I can build my header columns and parse out the data.
    ProjectSample.xlsx

    Thank you in advance for any and all suggestions.

    OldCityCat

  2. #2
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    146
    Articles
    0
    Excel Version
    Office 365
    You should best show the raw data before you edited it with VBA. Because normally you can do the complete preparation with Power Query, including the import from the web page.

    Furthermore, your example shows data in columns B and C that I can't find in A. You should pay attention to a coherent sample folder.

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,024
    Articles
    0
    Excel Version
    365
    I agree with pinarello's first paragraph in its entirety.
    I've put together a query which may take you further in the attached.
    Attached Files Attached Files

  4. #4
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    146
    Articles
    0
    Excel Version
    Office 365
    Hi p45cal,

    where can I read about it: Source = tbl,//#"Table1 (2)", ?

    I keep finding that I only know a fraction of Power Querty so far. However, even with this fraction of knowledge, one can realize a hell of a lot.


  5. #5
    Seeker OldCityCat's Avatar
    Join Date
    Apr 2017
    Location
    St Augustine FL
    Posts
    6
    Articles
    0
    Excel Version
    2016
    pinarello,

    Thank you for your response.

    Yes I agree, and I did attempt to prepare the raw data with Power Query, but because of my limited knowledge/experience, I opted to use vba to remove unnecessary data the produced the sample file.

    Sorry I did not explain my sample better, not sure what data was missing, A record consist of 3 rows starting with the ID:

    Because of HIPAA I could not send a more complete sample.

    I attempted to show the first row as a result of what I was trying to accomplish with Power Query, Once again because of my inexperience I failed.


    p45cal
    Thank you for your time and query.
    And for sharing "text" options for cleaning up raw data that I have never seen before.
    This opens a whole new area of learning for me.

    Thanks for sharing your knowledge.

    OldCityCat

  6. #6
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    138
    Articles
    0
    Excel Version
    Office 365
    Quote Originally Posted by p45cal View Post
    I agree with pinarello's first paragraph in its entirety.
    I've put together a query which may take you further in the attached.
    That's sweet... I'm really digging the use of the GroupBy command and with a little editing you can invoke your function (or embed the Text.Combine function) with in the GroupBy step directly - this can save you one or two steps.
    A tad more advanced, but it works nicely.

    Well done!
    Oh... by the way, YOU'RE WELCOME!

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,024
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by pinarello View Post
    where can I read about it: Source = tbl,//#"Table1 (2)", ?
    Ken Pul's M is for (Data) Monkey, chapter 21, 'Convert the query into a function'. Internet search for power query convert query to function does quite well.

    First thing to know is that I'm no expert and what I've done there is me experimenting, and what I have done has very likely been superseded by built-in functionality in the PW Editor, namely the right-click of a query in the expanded Queries panel at the left side of the PQ Editor and the option to Create Function… . I tried it but got flummoxed by the first thing that popped up: No parameters found. I've been meaning to explore it because I suspect it will make things a lot easier.

    So back to what I did. In the file I attached to msg#3, while editing the Table1 query, I'd got to the Grouped Rows stage where I had tables in the grp column and I needed to do some operations on each of those tables. So I duplicated the Table1 query which gave me a Table1 (2) table (only because I want to leave Table1 untouched) and I clicked on one of the tables in the grp column which gave me a 2-column, 3-row table. I then right-clicked the Table1 (2) query in the Queries panel and chose Reference. This creates a new query with the code:
    Code:
    let
        Source = #"Table1 (2)"
    in
        Source
    where #"Table1 (2)" (and Source) is one of those tables in the grp column of the Table1 query. At this point I do some transformations on that small table. As it happens, very little was required (only one line) but it could have been much more, and I ended up with:
    Code:
    let
        Source = #"Table1 (2)",
        #"Removed Other Columns" = Text.Combine(Table.SelectColumns(Source,{"Column1"})[Column1],"")
    in
        #"Removed Other Columns"
    Then it's a matter of converting that to a function by adding the top line which passes the parameter (the small table) to the code and changing the Source = #"Table1 (2)" line to use the parameter, which I did with Source = tbl, and I only commented out the #"Table1 (2)" so that I could easily revert to editing it as a query for tweaking:
    Code:
    (tbl)=>
    let
        Source = tbl,//#"Table1 (2)",
        #"Removed Other Columns" = Text.Combine(Table.SelectColumns(Source,{"Column1"})[Column1],"")
    in
        #"Removed Other Columns"
    So I chose to use tbl as the parameter name to hold the table in the (tbl)=>, I could have used more variables, and just need to substitute them in the later M code.
    I changed the name of the query/function to fnConcat, then used it in Table1 in the Invoked Custom Function step, which is directly after the Grouped Rows step.

    I deleted
    Table1 (2) query since it's no longer needed, and I should really have deleted the comment in the function.
    That's it.

    That function turned out to be so simple that it wouldn't be difficult to ditch it altogether and incorporate it into the Grouped Rows step as Nick Burns suggests.
    I could also have streamlined the function itself and the rest of the code but I was lazy!


    Last edited by p45cal; 2021-10-29 at 10:46 AM.

  8. #8
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    146
    Articles
    0
    Excel Version
    Office 365
    Hello p45cal,

    that's very nice of you to explain the procedure in such detail. So I can now build it myself once. Because only things that you have done yourself, burn better in the memory. By the way, I also have the book, but I haven't worked through it enough yet. Because mostly, if I have questions, then I try the dear aunt google.

Posting Permissions

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