How to get unique values of 3 data model tables in excel in a single data model table?
up vote
0
down vote
favorite
The issue is we have 3 databases that contain the names of our suppliers we also have data on those 3 databases that we want to merge into 1 pivot table. In order to do this, we need to get a list of unique items in order to do a many-many relationship match as shown in this post:
https://stackoverflow.com/questions/52286712/how-to-create-a-many-many-relationship-in-excels-data-model
microsoft-excel microsoft-powerbi
asked Dec 4 at 8:32
Pherdindy
298
add a comment |
up vote
0
down vote
favorite
The issue is we have 3 databases that contain the names of our suppliers we also have data on those 3 databases that we want to merge into 1 pivot table. In order to do this, we need to get a list of unique items in order to do a many-many relationship match as shown in this post:
https://stackoverflow.com/questions/52286712/how-to-create-a-many-many-relationship-in-excels-data-model
microsoft-excel microsoft-powerbi
asked Dec 4 at 8:32
Pherdindy
298
1
we need to get a list of unique items Copy all 3 data arrays onto a worksheet and use "Remove duplicates".
– Akina
Dec 4 at 9:07
Thank you didn't know you could do that. But my concern is if there will be new suppliers that enter the picture. We use auto-refresh on our data models and if there is a missing supplier then it'll be missing in the pivot table I believe
– Pherdindy
Dec 4 at 9:24
Can this be done using DAX? I'm very unfamiliar with it
– Pherdindy
Dec 4 at 9:30
You need to combine all 3 lists into one list (UNION in SQL terms). Yes, DAX can it. See, for example, this article.
– Akina
Dec 4 at 9:44
I have no idea how to place it in theRowsportion of the pivot table though. I usedCustomer:=DISTINCT(UNION(VALUES('TestCur - Invoice'[CustomerID],'TestDR - Invoice'[CustomerID])))but returns an error
– Pherdindy
Dec 4 at 10:17
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
The issue is we have 3 databases that contain the names of our suppliers we also have data on those 3 databases that we want to merge into 1 pivot table. In order to do this, we need to get a list of unique items in order to do a many-many relationship match as shown in this post:
https://stackoverflow.com/questions/52286712/how-to-create-a-many-many-relationship-in-excels-data-model
microsoft-excel microsoft-powerbi
asked Dec 4 at 8:32
Pherdindy
298
The issue is we have 3 databases that contain the names of our suppliers we also have data on those 3 databases that we want to merge into 1 pivot table. In order to do this, we need to get a list of unique items in order to do a many-many relationship match as shown in this post:
https://stackoverflow.com/questions/52286712/how-to-create-a-many-many-relationship-in-excels-data-model
microsoft-excel microsoft-powerbi
microsoft-excel microsoft-powerbi
asked Dec 4 at 8:32
Pherdindy
298
asked Dec 4 at 8:32
Pherdindy
298
asked Dec 4 at 8:32
Pherdindy
298
asked Dec 4 at 8:32
Pherdindy
298
asked Dec 4 at 8:32
Pherdindy
298
298
1
we need to get a list of unique items Copy all 3 data arrays onto a worksheet and use "Remove duplicates".
– Akina
Dec 4 at 9:07
Thank you didn't know you could do that. But my concern is if there will be new suppliers that enter the picture. We use auto-refresh on our data models and if there is a missing supplier then it'll be missing in the pivot table I believe
– Pherdindy
Dec 4 at 9:24
Can this be done using DAX? I'm very unfamiliar with it
– Pherdindy
Dec 4 at 9:30
You need to combine all 3 lists into one list (UNION in SQL terms). Yes, DAX can it. See, for example, this article.
– Akina
Dec 4 at 9:44
I have no idea how to place it in theRowsportion of the pivot table though. I usedCustomer:=DISTINCT(UNION(VALUES('TestCur - Invoice'[CustomerID],'TestDR - Invoice'[CustomerID])))but returns an error
– Pherdindy
Dec 4 at 10:17
add a comment |
1
we need to get a list of unique items Copy all 3 data arrays onto a worksheet and use "Remove duplicates".
– Akina
Dec 4 at 9:07
Thank you didn't know you could do that. But my concern is if there will be new suppliers that enter the picture. We use auto-refresh on our data models and if there is a missing supplier then it'll be missing in the pivot table I believe
– Pherdindy
Dec 4 at 9:24
Can this be done using DAX? I'm very unfamiliar with it
– Pherdindy
Dec 4 at 9:30
You need to combine all 3 lists into one list (UNION in SQL terms). Yes, DAX can it. See, for example, this article.
– Akina
Dec 4 at 9:44
I have no idea how to place it in theRowsportion of the pivot table though. I usedCustomer:=DISTINCT(UNION(VALUES('TestCur - Invoice'[CustomerID],'TestDR - Invoice'[CustomerID])))but returns an error
– Pherdindy
Dec 4 at 10:17
1
1
we need to get a list of unique items Copy all 3 data arrays onto a worksheet and use "Remove duplicates".
– Akina
Dec 4 at 9:07
we need to get a list of unique items Copy all 3 data arrays onto a worksheet and use "Remove duplicates".
– Akina
Dec 4 at 9:07
Thank you didn't know you could do that. But my concern is if there will be new suppliers that enter the picture. We use auto-refresh on our data models and if there is a missing supplier then it'll be missing in the pivot table I believe
– Pherdindy
Dec 4 at 9:24
Thank you didn't know you could do that. But my concern is if there will be new suppliers that enter the picture. We use auto-refresh on our data models and if there is a missing supplier then it'll be missing in the pivot table I believe
– Pherdindy
Dec 4 at 9:24
Can this be done using DAX? I'm very unfamiliar with it
– Pherdindy
Dec 4 at 9:30
Can this be done using DAX? I'm very unfamiliar with it
– Pherdindy
Dec 4 at 9:30
You need to combine all 3 lists into one list (UNION in SQL terms). Yes, DAX can it. See, for example, this article.
– Akina
Dec 4 at 9:44
You need to combine all 3 lists into one list (UNION in SQL terms). Yes, DAX can it. See, for example, this article.
– Akina
Dec 4 at 9:44
I have no idea how to place it in the
Rows portion of the pivot table though. I used Customer:=DISTINCT(UNION(VALUES('TestCur - Invoice'[CustomerID],'TestDR - Invoice'[CustomerID]))) but returns an error– Pherdindy
Dec 4 at 10:17
I have no idea how to place it in the
Rows portion of the pivot table though. I used Customer:=DISTINCT(UNION(VALUES('TestCur - Invoice'[CustomerID],'TestDR - Invoice'[CustomerID]))) but returns an error– Pherdindy
Dec 4 at 10:17
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
I would prefer to work in the Query Editor using the Append feature to combine the data from the 3 databases into one table. All your data modeling and visualisation tasks will be simpler from then on, for example you wont need to build this table.
However if you just want a quick fix using DAX, it would be along these lines:
All Customers =
UNION (
UNION (
DISTINCT ( 'Invoice1'[CustomerID] ),
DISTINCT ( 'Invoice2'[CustomerID] )
),
DISTINCT ( 'Invoice3'[CustomerID] )
)
answered Dec 15 at 0:17
Mike Honey
1,7411511
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1380622%2fhow-to-get-unique-values-of-3-data-model-tables-in-excel-in-a-single-data-model%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
I would prefer to work in the Query Editor using the Append feature to combine the data from the 3 databases into one table. All your data modeling and visualisation tasks will be simpler from then on, for example you wont need to build this table.
However if you just want a quick fix using DAX, it would be along these lines:
All Customers =
UNION (
UNION (
DISTINCT ( 'Invoice1'[CustomerID] ),
DISTINCT ( 'Invoice2'[CustomerID] )
),
DISTINCT ( 'Invoice3'[CustomerID] )
)
answered Dec 15 at 0:17
Mike Honey
1,7411511
add a comment |
up vote
0
down vote
I would prefer to work in the Query Editor using the Append feature to combine the data from the 3 databases into one table. All your data modeling and visualisation tasks will be simpler from then on, for example you wont need to build this table.
However if you just want a quick fix using DAX, it would be along these lines:
All Customers =
UNION (
UNION (
DISTINCT ( 'Invoice1'[CustomerID] ),
DISTINCT ( 'Invoice2'[CustomerID] )
),
DISTINCT ( 'Invoice3'[CustomerID] )
)
answered Dec 15 at 0:17
Mike Honey
1,7411511
add a comment |
up vote
0
down vote
up vote
0
down vote
I would prefer to work in the Query Editor using the Append feature to combine the data from the 3 databases into one table. All your data modeling and visualisation tasks will be simpler from then on, for example you wont need to build this table.
However if you just want a quick fix using DAX, it would be along these lines:
All Customers =
UNION (
UNION (
DISTINCT ( 'Invoice1'[CustomerID] ),
DISTINCT ( 'Invoice2'[CustomerID] )
),
DISTINCT ( 'Invoice3'[CustomerID] )
)
answered Dec 15 at 0:17
Mike Honey
1,7411511
I would prefer to work in the Query Editor using the Append feature to combine the data from the 3 databases into one table. All your data modeling and visualisation tasks will be simpler from then on, for example you wont need to build this table.
However if you just want a quick fix using DAX, it would be along these lines:
All Customers =
UNION (
UNION (
DISTINCT ( 'Invoice1'[CustomerID] ),
DISTINCT ( 'Invoice2'[CustomerID] )
),
DISTINCT ( 'Invoice3'[CustomerID] )
)
answered Dec 15 at 0:17
Mike Honey
1,7411511
answered Dec 15 at 0:17
Mike Honey
1,7411511
answered Dec 15 at 0:17
Mike Honey
1,7411511
answered Dec 15 at 0:17
Mike Honey
1,7411511
1,7411511
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1380622%2fhow-to-get-unique-values-of-3-data-model-tables-in-excel-in-a-single-data-model%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
we need to get a list of unique items Copy all 3 data arrays onto a worksheet and use "Remove duplicates".
– Akina
Dec 4 at 9:07
Thank you didn't know you could do that. But my concern is if there will be new suppliers that enter the picture. We use auto-refresh on our data models and if there is a missing supplier then it'll be missing in the pivot table I believe
– Pherdindy
Dec 4 at 9:24
Can this be done using DAX? I'm very unfamiliar with it
– Pherdindy
Dec 4 at 9:30
You need to combine all 3 lists into one list (UNION in SQL terms). Yes, DAX can it. See, for example, this article.
– Akina
Dec 4 at 9:44
I have no idea how to place it in the
Rowsportion of the pivot table though. I usedCustomer:=DISTINCT(UNION(VALUES('TestCur - Invoice'[CustomerID],'TestDR - Invoice'[CustomerID])))but returns an error– Pherdindy
Dec 4 at 10:17