Log In
Register
Home
March 18, 2020
Offline
Hi
At work I have to use data, where the key is the same in a number of source files, that contains necessary data for the purpose of a Power Query solution. The key is "Case#" and as mentioned is the same in every source data files.
I know, I could merge tables, but that doesn't really work for me.
Has any of you a tip how to adjust/ manipulate or otherwise the keys to make it possible to create a working data model ?
Thanks
Erik
October 5, 2010
Online
Hi Erik,
I'm having trouble picturing things without seeing the data. Can you please attach some sample files/data and an example of the final result you want.
Regards
Phil
July 16, 2010
Offline
Hi Eric,
Maybe what you need to use is Power Pivot to create relationships between your tables using the Case# field. However, like Phil mentioned, it's difficult to say without an example of the final result you want.
Mynda
March 18, 2020
Offline
Hi Mynda and Phil
The data is from work and rather sensible, but as an example this could be part of a file:
- District - number and text - I only use these data from my main source file and drag it in where needed in pivot tables. I split the number and text
- Commune - same
- Zip code and mail district - same
- Case# - here is my issue - data is the sum of data from 12 source files, where the only key/ common denominator is this
- The Case# consists of unique number values saved as text, because they have 16 digits/numbers for instance: "5100764050002821"
- The 11 "sub"- files gives information about different steps in handling a case, different outcomes, economy and staff involved
Hope this is enough to describe the challenge ?
Regards
Erik
From a number of Power Query solutions I have never made more than one connection on key, and never had to handle more than 2 source files. These I have handled by merging the two files into one
But merging data from 11 different files doesn't really make sense.
Since I finished the dashboard course, I have made quite a lot of reports and have had very few problems with my data model, but this time I find myself in trouble.
July 16, 2010
Offline
Hi Erik,
Still very difficult to create an image in my head from your description as to what the issue is. Usually when dealing with sensitive data you can create a small mock-up of your data with the sensitive parts redacted or replaced with dummy values.
However, it still sounds like you should be using Power Pivot to create relationships between your district, commune, zip code and mail district table and another table containing the data from your 12 source files. Both tables will contain the case# so you can relate them.
Also, I suspect the data from each file needs to be identified with maybe a date or something so you can tell which file it came from after putting it into one table. These 12 files will be appended, not merged.
Hope that points you in the right direction, but happy to help if you're still stuck if you can provide a file that illustrates the issue.
Mynda
March 18, 2020
Offline
Hi Mynda and Phil
I have made a file with simple mockup data and in two sheets tried to show to show you my challenge and my idea of a solution.
As mentioned only the case # is a common denominator - each of the files contains data from different steps in the work proces, and contains of 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.
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.
Added 25-03-2022 - I obviously didn't succeed in uploading my mock-up file - done that now
Thank you
Erik
1 Guest(s)
