Hi
I have large set of data in different tables, Is there a way I can get all the data from 3 tables and consolidate it into one single table?
In the attached file you I have tried to create a powerpivot in which the only "Unique" filed available is Supplier ID.
Supplier ID is repeated in all tables but when I connect all tables, i can get the data only from "supplier spend" table.
How and what tool can I use to consolidate the data into one masterfile?
Thanks
It's not clear what your required output actually is?
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
It depends on what exactly you want to do.
But, looking at your tables. There isn't enough key to tie all the tables together it looks like.
Ex: SupplierID is what ties all tables together. But there is no other key in SupplierSpend fact table. This will cause ambiguity in the data model when attribute from SupplierList/SupplierEmail dimension tables are used, and will result in erroneous/unexpected result in DAX calculaiton.
Also, your current model is not ideal for data model. You have single dimension table that connect to both the fact table and other dimensions.
For BI and data modeling, you should consider designing star schema (or fact constellation scheme if more than 1 fact table), where all dimensions are connected directly to fact table.
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Hi;
in the second attachment I have replicated manually the desired Output (see tab Required Output line "30"), which is coming from the 3 Tabs:
SupplierSpend
SupplierList
SupplierEmail
Thanks
Ok, so PowerPivot isn't really the right tool for this. Since there is relationship ambiguity as I mentioned from SupplierList and SupplierEmail to record in SupplierSpend fact table.
Though you could simulate it using multiple pivot tables...
But it's nothing more than 3 separate pivots that show attributes tied together by slicer.
This sort of operation is more in realm of application coding (in Excel via VBA).
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however the thread title does not really convey what your request is about.
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks