Split Data in several tabs

A

albertan

Member
Joined
Nov 9, 2017
Messages
30
Reaction score
0
Points
6
Location
Canada
Excel Version(s)
2013
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
 
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?
 
Ken Puls said:
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/modifying-power-query-m-code-with-vba/
Click to expand...

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.
 
Bob Phillips said:
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.
Click to expand...


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.
 
albertan said:
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.
Click to expand...

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
 
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
 
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.
 
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
 
You must log in or register to reply here.
Back
Top