New table from rows that meet condition

B

beagleton

New member
Joined
Jun 28, 2017
Messages
26
Reaction score
0
Points
0
Hello All,

Is it possible to create a new table from select rows in another table? For example, if you have a table with columns [Name], [Time], [Value], [State], and multiple rows, is it possible to say:

if [Name] contains "smith", put into new table named "Smith Records" ?

Ideally, all of the columns and row data for rows with names containing Smith would now be in a new table which could be viewed separately in the workbook queries tab. Even more ideally, you could set whether this new table is connection only, is loaded in the workbook, or is loaded in the data model.


Please let me know what you think.
Thanks,
Brendan
 
Sort of.

The way I would do this is to right click the query (in the queries pane) with all the data, then choose Reference. Next you can set your filters as desired then load it wherever you want.

Basically it creates a pointer to the prior table that you can add additional layers too. Does that make sense?


Sent from my iPhone using Tapatalk
 
It does make sense ... I am wondering how this will affect performance though. Does the original query need to load/process every time the new query is invoked? Part of the reason I'm doing this is to reduce the burden on the system when processing large datasets. If the two queries are still "linked" I am worried that it may undermine the purpose. I guess I can always just filter the original query, load to the data model, export with DAX studio to CSV and then load as a new query if I want them to be totally independent. I guess I was just hoping that PQ had a baked in method of doing this without all the steps.
 
So here's the deal... even if you created a query to "push" the data into a new table, it would essentially do the same thing. By creating a reference, the idea is that it SHOULD read the steps of the previous query and re-use (rather than re-execute) them. Your additional steps then get added to further refine the data. This is exactly how things work in Power BI Desktop today.

Full disclosure, there is a challenge in Power Query in Excel today (but not in Power BI), where child nodes do get re-executed. Help me get that changed by voting here: http://xlguru.ca/fixpq

But let's back up... Where are you calling your data from? A server or files? Ideally, if you are suffering performance, you should try and connect directly to the database and push as much to query folding as possible. That offloads the work to the server to process, rather than your local Excel instance, and should speed up query refresh times.
 
I would love to connect directly to a database and allow for use of query folding. Unfortunately, I don't have access to the data source and so I have to deal with CSV files. lots and lots of huge csv files :(

Voted btw.
 
Dan Bliss said:
Gil Raviv recently posted how to use VBA to automate PQ using Excel 2016
Click to expand...

The only kicker here is that VBA can't help the underlying Power Query latency issue. In fact, if you're loading data to Power Pivot and controlling refreshes manually, you can actually slow things down.

Back to the issue at hand, I'm afraid it's bad news. CSV's have no engine, so the data gets brought in locally to process. Any chance that IT would let you install SQL Server Express, or allow you to use Power BI Desktop and push the data into the Power BI service?

Doing either would at least allow you to separate the data load from the reporting layer...
 
You must log in or register to reply here.
Back
Top