Results 1 to 9 of 9

Thread: Split Data in several tabs

  1. #1
    Acolyte albertan's Avatar
    Join Date
    Nov 2017
    Location
    Canada
    Posts
    22
    Articles
    0
    Excel Version
    2013

    Split Data in several tabs



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

    I know that we can append several tabs together and come up with a consolidated query. What if the other way around I will get a Data and I will need to split each project listed in this Data into several tabs?

    I was thinking to manually filter the project in a Query, then copy the script and do it for another query by changing the project number.

    I also thought to perhaps using a parameter query but in this case I do not want a drop down list of projects. If I put them all in one table then I will have to change a parameter query for each record as "{0}[Column1], "{1}[Column1] etc so there will be parameter queries for each project number (same as if I would just create extra queries with project filtered for each).

    Or would the VBA be better in this case, not sure what option and what VBA might be useful here.

    Any advice is appreciated

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,343
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Maybe have a look at Gasper's page on using VBA to modify queries. It seems to be targeted at what you're after: http://excelunplugged.com/2018/01/23...code-with-vba/
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,248
    Articles
    0
    Excel Version
    Office 365 Subscription
    I was looking at this myself yesterday: is it really not possible to generate the individual tables using M Code? Is a parameter query the closest you can get in PQ itself?
    Ali
    Enthusiastic self-taught user of MS Excel!

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by Ken Puls View Post
    Maybe have a look at Gasper's page on using VBA to modify queries. It seems to be targeted at what you're after: http://excelunplugged.com/2018/01/23...code-with-vba/
    This is one example where VBA is actually useful with Power Query. In VBA you can load the initial query, then modify it creating new queries for each required query and load them. In the VBA you can determine whether to load the results to Excel or just create a connection, and whether to add it to the datamodel or not. I have a VBA addin that does lots of this sort of thing, including pulling m functions in from a library of functions, and so on. It is a useful technique for a Power query developer, one that I demonstrated at SQL Saturday #890 in Austin on 13th July.

  5. #5
    Acolyte albertan's Avatar
    Join Date
    Nov 2017
    Location
    Canada
    Posts
    22
    Articles
    0
    Excel Version
    2013
    Quote Originally Posted by Bob Phillips View Post
    This is one example where VBA is actually useful with Power Query. In VBA you can load the initial query, then modify it creating new queries for each required query and load them. In the VBA you can determine whether to load the results to Excel or just create a connection, and whether to add it to the datamodel or not. I have a VBA addin that does lots of this sort of thing, including pulling m functions in from a library of functions, and so on. It is a useful technique for a Power query developer, one that I demonstrated at SQL Saturday #890 in Austin on 13th July.

    I reviewed the VBA code and would like to see if Replace formula can be set to dynamic (in this case it refers to "Company 1")

    If i have a list of items mentioned in the range, not sure if there could be a step automated so that the query will read it from "{0}[Column1] and move it on to "{1}[Column1] automatically.

    I would think that Power Query will be further improved and there will be a feature available as a "contra" to consolidation.

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by albertan View Post
    I reviewed the VBA code and would like to see if Replace formula can be set to dynamic (in this case it refers to "Company 1")

    If i have a list of items mentioned in the range, not sure if there could be a step automated so that the query will read it from "{0}[Column1] and move it on to "{1}[Column1] automatically.
    That would be easy enough. Something like this pseudo-code

    Code:
    For i = 2 to mycompanies.rows.count 'assumes the basic query looks at {0}
    
        newyquery = Replace(Originalquery,"{0}[Column1]", "{ & I-1 & "][Column1]")
        newquery.add 
    Next i

  7. #7
    Acolyte albertan's Avatar
    Join Date
    Nov 2017
    Location
    Canada
    Posts
    22
    Articles
    0
    Excel Version
    2013
    I'm not sure this code would work. You replace script in an existing query and then add it? Shouldn't the query be referenced first/duplicated and then the code replaced? Do you happen to have a full code, thank you

  8. #8
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    You are right, but I had assumed you got that from the article referenced, I was just referring to what you need to add. I do not have the full code to hand, and I am off out shortly. I will try and knock it up later today for you.

  9. #9
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    Here is the code.
    A few details about what it is doing, and the data it is working on:
    - a table of company data, called tblCompanyData.
    - another table of companies,tblCompany,that I will create a new query for each company therein.
    - a basic query named Company1 that sets up a variable that gets the first value in tblCompany, loads the company data table, and filters the [Company]column for the value in that variable.
    This is the first M query
    Code:
    let
        _rownum = 0,
        target = Excel.CurrentWorkbook(){[Name="tblCompany"]}[Content]{_rownum}[Company],
        Source = Excel.CurrentWorkbook(){[Name="tblCompanyData"]}[Content],
        #"Filtered Rows" = Table.SelectRows(Source, each ([Company] = target))
    in
        #"Filtered Rows"
    So that is the initial Power Query setup that the VBA will work on. The VBA will:
    - reads the initial query into memory
    - read the number of rows in tblCompany into memory
    - within a loop of 2 to tblCompany.rows.count:
    - amend the initial query to get the next row from tblCompany
    - write this as new query named this value in tblCompany
    - end code

    This is the VBA

    Code:
    Dim wb As Workbook
    Dim qry As WorkbookQuery
    Dim companies As ListObject
    Dim qrycode As String
    Dim newcode As String
    Dim i As Long
        Set wb = ActiveWorkbook
        Set qry = wb.Queries("Company1")
        qrycode = qry.Formula
        
        With ActiveSheet.ListObjects("tblCompany").DataBodyRange
        
            For i = 2 To .Rows.Count
            
                newcode = Replace(qrycode, "_rownum = 0", "_rownum = " & i - 1)
            
                wb.Queries.Add Name:=.Cells(i, 1).Value, _
                               Formula:=newcode, _
                               Description:=.Cells(i, 1).Value & " - query auto-generated"
            Next i
        End With
        
        Set wb = Nothing
        Set qry = Nothing
    End Sub

Posting Permissions

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