Import union DAX statement into data model?

Copper Contributor

Good morning. So my first question was a silly one, but now that is working I need to extend it a bit. 

I was using a DAX table to construct a list of unique keys for linking things together. 
It works great, and now I have another set of SKUs in a different column that I would like to add to the end of the table.

We're not talking massive tables here - in SQL I would just do a union statement, and it turns out  there is something similar in DAX! Happy days!

I looked up union statements, copied the first formula in, changed some col IDs and it worked first time. It references different cols, but the output is identical - same column headers etc.

However, even though the table is perfect on screen, when I refresh the data model to import the updated table it fails. Is it something I did or a limitation of Excel? 

The error message is:
"We couldn’t refresh the connection. Please go to existing connections and verify they connect to the file or server."

 

As soon as I take the UNION out it works again.


Here is the DAX:

EVALUATE
UNION (
CALCULATETABLE(
SELECTCOLUMNS('Products - database',  
              "Lookup", 'Products - database'[first lookup],
              "SKU", 'Products - database'[distributor_sku],
              "Cost Price",'Products - database'[cost],
              "Counter",count('Products - database'[first lookup])
)
,'Products - database'[first lookup] <>""
,'Products - database'[cost] >0
)
,
CALCULATETABLE(
SELECTCOLUMNS('Products - database',  
              "Lookup", 'Products - database'[second lookup],
              "SKU", 'Products - database'[distributor_sku],
              "Cost Price",'Products - database'[cost],
              "Counter",count('Products - database'[second lookup])
)
,'Products - database'[second lookup] <>""
,'Products - database'[cost] >0
)
)

 

Thanks,
Steve

PS
I am using externally provided data, so changing the input data isn't an option - I need to work with what they send me.

 

8 Replies

Hi @lowndsy 

 

Not sure on your approach here. 

 

Have you used Power Query to pull data into the data model?

 

 

@Wyn Hopkins 


I don't think so - just Power Pivot, if that makes sense? I got the method I used from a recommendation on a blog somewhere, but if there is a better way I'll happily do that.

 

The aim is just to get one perfect list of unique keys on one side of the 1: many relationship so I can enrich it with data from the csv data, which is full of blanks.

1) Import csv files into Data model (legacy text wizard, connection only, prompt for file name on refresh)

2) Dump some columns back into the sheet as a table using a DAX query - with a few filters

3) Add that cleaned table back to the data model as a new source with power pivot > add to data model 

 

Step 3 Is where it fails - when I use a UNION to run the same query but on different columns, Excel gets upset, which is understandable because it is connecting to the same table twice. 


The csvs come from an external system and I want to avoid manually editing them.
I am basically building something where anyone in the office can click "refresh all", select the latest files and go for a cup of tea while it crunches away.


I am happy enough with what I have so far, but it means I need there copies of the same system. Getting the UNION thing going (or equivalent) would be the icing on the cake and let me smush it all together into one file.
I would be a hero in the office. Gentlemen doff their caps, ladies would want to know me. Doors would open and Dogs would wag their tails at me. Or it could just save our us a load of pointless work every day - I'm easy.

Thanks,
Steve

@Wyn Hopkins 

 

@Wyn Hopkins 


I don't think so - just Power Pivot. I got the method I used from a recommendation on a blog somewhere, but if there is a better way I'll happily do that.

 

The aim is just to get one perfect list of unique keys on one side of the 1: many relationship so I can enrich and link the csv data.

1) Import csv files into Data model (legacy text wizard, connection only, prompt for file name on refresh)

2) Dump some columns back into the sheet as a table using a DAX query - with a few filters

3) Add that cleaned table back to the data model as a new source with power pivot > add to data model 

 

Step 3 Is where it fails - when I use a UNION to run the same query but on different columns, Excel gets upset, which is understandable because it is connecting to the same table twice. 


The csv files come from an external system and I want to avoid manually editing them.
I am basically building something where anyone in the office can click "refresh all", select the latest files and go for a cup of tea while it crunches away.


I am happy enough with what I have so far, but it means I need three copies of the same system. Getting the UNION thing going (or equivalent) would be the icing on the cake and let me smush it all together into one file.
I would be a hero in the office. Gentlemen doff their caps, ladies would want to know me. Doors would open and Dogs would wag their tails at me. Or it could just save our us a load of pointless work every day - I'm easy.

Thanks,
Steve

@lowndsy 

Perhaps I misunderstood your logic - tried to repeat on very simple model and UNION works.

 

However, from my point of view now that's a bit outdated technique. Such all transformations is more natural to do with Power Query.

Hi @lowndsy 

 

If you are using Excel 2013 or later then I'd strongly recommend a different approach and use Power Query to connect to your CSV files, clean the data up and load directly to Power Pivot.

 

This results in a fully automated, refreshable report,  and your goal of hero status will be fulfilled :rolling_on_the_floor_laughing:

 

With Power Query you can do all sorts of data manipulations, merges, appends etc to get your data into the right shape for the Data Model

 

 

That makes sense. More than happy to jump over to Power Query, and I have even played around with it in the past, but I the thing that stopped me using it in anger was importing new files.

The legacy text import has "prompt for file name on refresh", so when the user clicks "refresh all" they see a browse window to specify which file(s) to import.
Renaming files and copying them into the same location isn't feasible for my users - I guarantee the people using it would mess it up within days and stop using it completely within a week.

 

I found other people asking about this feature and the answer seems to be no?
https://superuser.com/questions/1496004/can-power-query-prompt-for-a-file-name-when-loading-a-text-f...

Does anyone have a solution?

You can't get it to prompt for a file name but you could allow users to enter the filename / path into a named cell

 

https://accessanalytic.com.au/powerquery_namedcells_parameters/

 

 

Sadly I'll have to stick to what I have, perhaps see if I can tweak the DAX statement to pull everything I need it to.