I have made a file with simple mockup data and in two sheets trying to show to show you my challenge and my idea of a solution, when the key data is the same in every data source.
Only the "case #" is a common denominator - each of the files contains data from different steps in the work proces, and data format can be text, names, dates, costs, numbers and status.
Data are extracted as *.xlsx or *.csv files from QlikView and cannot be modified in QlikView.
Data will be updated in QlikView daily and in the Excel Power Query solution ( Dashboard ) on a weekly basis.
My place of work is a public institution, and Excel 2016 is the only "tool" available.
I have made a lot of other reports, dashboards and solutions using Power Query and in a number of cases used data modelling, but here it turns out to be a major problem to create a data model, because case# is key in all of the source data files.
In short - my idea is to add a column to each of the underlying files with a copy of the case # - e.g. "A_Case #", "B_Case #"..... and in the main file create a number of columns - all containing the case # with a preceeding letter to be able to make connections to each of the underlying files.
I hope you can will evaluate my idea and give it a go or maybe give me an alternative solution.
Thank you
Erik
Hi Erik,
It's not clear what you want to do: you want to combine all source files, or to add each source file as a different table in the data model to process data with Power Pivot?
Will be much clear if you can show an example with how the final data should look like, we will be able to provide a solution to get to that output.
Hi Catalin
I want to combine all data source files with my main file and take parts of data from these in Power Pivot and charts.
Erik
Hi Catalin
Thanks - but this is not the issue with my data sources.
They consist of up to 12 different data files with different contents, and only the "Case #" is unique and a common denominator for all source files.
That is the reason for my possible solution with copying the Case# collumns and rename it "A-Case#", "B-Case#" and so on in the main file. And in each of the source files pick one of the headlines, so that each source file has got a unique key, that can be used to form a data model.
How does that sound to you or others reading this ?
Erik
If you combine the files with the solution described in the link, you will get data like this:
Isn't that what you want? You said that you want to combine data from folder.
you're saying that you want to form a data model.
Let me ask again:
you want to combine all source files, or to add each source file as a different table in the data model to process data with Power Pivot?
Hi Catalin
I am not going to combine the data source files in Power Query - only in the data model. With unique column headers ( copying the case# and adding for instance "A_" should do the trick - I hope.
Doing that I hope to be able to take data from the different data source tables into Power Pivot and create reports.
Erik
Hi Erik,
There is no such thing as combining data in data model, in data model there will be individual tables and you can create relationships between tables.
You need to organize the data model in a star schema, but for this you have to create a dimension table - a list of unique Case # that will be the center of your model, all data sources will be related to this unique list of case #. The reason for this is that all your fact tables you have may contain duplicate case #.
Hi Catalin
That is what I have been trying to suggest - maybe in other words - but this was my idea.
Guess you agree with me, that it can be done this way.
