Hot Mess of a data sets

[fmaignan]

I am sure someone far more adept than I am in excel may have solution to this.  I am trying to use Power Query to pull data from a Laboratory Information Management System (LIMS).  The data is organized in tables that range from 4,500 rows to over 2 million rows.  The ultimate goal is to have a user paste a list of lot numbers on a worksheet and press a VBA button to return specific numeric data from testing results.  The challenge here is that the various LIMS data tables have varying relationships that I cannot recreate in Power Query or the Data model.  Excel tells me these are "many to many" relationships that are not supported.  I can use Crystal Reports to build a report just fine but that has to be exported to Excel and cleaned up.  I can provide as much detail as possible if someone has ideas/suggestions.

[BA_Pete]

Hi @fmaignan ,

Database relationships aren't the same thing as data model relationships. DB relationships generally just identify fields that contain the same data and aren't really concerned with cardinality.

To get your tables to work for you in Excel, you're going to need to construct a Star Schema model:

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema 

Broadly speaking, you will need to find or create dimensions tables that only contain unique values of filterable dimensions, that you can relate on a one-to-many basis to each of your fact tables. In excel, this will involve adding all of your tables to the native data model then creating the relationships between them.

If you can share a small example of one of your fact tables with sensitive data removed, I can give you an example of how to create a dimension table and relationship from it using Power Query and the data model.

Pete

[fmaignan]

Thank you Pete.  Not sure if this helps, but this is what I see in Crystal Reports.

I don't think I have the Power BI tools loaded on my laptop.  I could petition our IT folks for it, but we are unfortunately in a pretty tightly controlled environment manufacturing pharmaceuticals.  I am sure there is a way to pull the data into Excel to allow for sorting/querying, but I am afraid I've bitten off more than I can chew on this one.

[BA_Pete]

Hi @fmaignan ,

Difficult for me to give an example query working from a screenshot, but I can say that the relationships you're seeing there are DB relationships. They're just telling you which fields are 'the same' as each other, rather than having any useable cardinality for a data model.

You aren't necessarily going to need Power BI to do any of this either. The article I linked is just about the creation of star schema structure models, and will work just as well using the Excel native data model features if you want/need to go that far.

Based on the limited info I have available, I would broadly recommend starting out like this:

I'm assuming for the sake of example that the 'lot numbers' your users are going to be pasting into your worksheet are the [ID_Numeric] values from your SAMPLE table.

1) Connect to your DB from Power Query (PQ) and import your SAMPLE table. Call it 'SAMPLE'.

2) Create a named table on a sheet where you want users to paste in ID numbers. In PQ, import this named table into a new query called 'inputID', and call the column 'inputID' as well.

3) Back in your SAMPLE query, filter the [ID_Numeric] column and select any ID value to filter. We're just doing this to quickly create the query step structure we need. You should see the code generated for this step in the formula bar in PQ.

4) Edit the code generated in step 3 so it looks something like this:

= Table.SelectRows(previousStepName, each List.Contains(List.Buffer(inputID[inputID]), [ID_Numeric]))

//Where 'previousStepName' should have been auto-filled for you

This will filter your SAMPLE table where only the ID values that are found in the pasted user list, via the inputID query, are shown.

5) Select 'Close and Load To...' and choose where you want the tables to go to. I would suggest that you would want to apply the inputID table as 'Connection Only', as you have no real use for this data beyond its use in filtering the fact table(s).

You should now have the basic structure to enable the following process to control what data is brought into your worksheets/data model:

- User pastes IDs into named table

- User refreshes existing connections

- PQ runs queries, filters fact table(s), and sends back to wherever you decided the data should go for reporting.

It's clearly going to get more complicated than this when you start factoring in your other tables and what you want/need to do with them but, as above, I'm working pretty blind here, so this is just the concept that I would start out with.

Pete