Creating new table in MS Excel 2013 PowerPivot

dluhut

New Member
Joined
Feb 24, 2012
Messages
21
Hello,

Although I won't say that I'm a beginner in Excel, but I'm relatively new with Power Pivot.

Had been playing around with it and watch many videos. However, I have an issue and hope that this forum could help me out.

Say I have 3 tables that have a column 'id' where none of them are unique.

I need to know 2 things:

  1. How can I create a new table in 'data model'?
  2. How can I insert the distinct/unique ID from each table onto the newly created table?

FYI, I'm using MS Excel 2013, and I'm trying to avoid creating the 4th table in Excel (that is putting together each of the ID and make a pivot table out of it to get the distinct/unique ID. Reason being, I'd like to when I refresh the 'table', the 4th table can automatically populated itself on the data model.

Thank you in advance for your help.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi,

I believe some extra info might help us to better understand the issue. You could post some samples of your current tables and the expected results. I have a couple of questions as well:
  • You are saying that the three tables have no unique ID's, but you want to consolitdate the unique ID's in a fourth table. Do you intend to create unique ID's for each row or only join unique values?
  • Where do your tables come from? Are they linked tables on a spreadsheet, data pasted into Power Pivot, external data loaded through PowerPivot or through Power Query?

Near the end of the Forum Guidelines you can find several tools you can use to post your data samples as tables.
 
Upvote 0
Hi FranzV,

I don't know if the sample would do any better in explaining, because I wasn't able to produce the result...Let me see if there's a way that I can attached a sample...from my understanding, this forum doesn't allow an attachement

Answer to your questions:
You are saying that the three tables have no unique ID's, but you want to consolidate the unique ID's in a fourth table. Do you intend to create unique ID's for each row or only join unique values?
Ans: To be honest, I don't know the difference between 'create unique IDs for each row' and 'join unique vallues'. What I'm trying to accomplish is given the number of IDs' in each table, list all of them in a new (fourth) table but only showing unique.
Another way that I can put it is, if I were to do it in Excel manually, I'll copy all of the account ID from each table and put it into 1 column, and then create a pivot table, and the result that the pivot table showed me will list all of the ID.

Where do your tables come from? Are they linked tables on a spreadsheet, data pasted into Power Pivot, external data loaded through PowerPivot or through Power Query?
Ans: The tables are in the Excel spreadsheet which are actually an output from SAP BW via Analysis add ins. It is then put into Power Pivot via 'Add to Data Model'. No Power Query, as company didn't have this installed, and thus it's useless if I were to do it via Power Query as other's won't be able to get the data that's from the output of Power Query.

EDIT: I'm unable to install the add-ins as I don't have admin rights in corporate laptop.
 
Last edited:
Upvote 0
XqXhohf.png
 
Last edited:
Upvote 0
Power Pivot in Excel doesn't allow you to add tables using formulas only. You need to import them from a source or paste the data directly into Power Pivot. However, you can get the desired results using a helper table that only serves as a base for other calculations. I created a dummy file that uses a table with consecutive numbers starting at one and should have at least one row for each unique account. If you don't know the max number of accounts, just use a number that is large enough (eg. in the dummy there are 10 distinct accounts but the HelperTable has 1,000 rows, just to be on the safe side). Of course, it is better to keep your table as small as possible to avoid performance issues.

The file looks like:

Excel 2016 (Windows) 64 bit
ABCDEFGHIJKLM
1
2
AccountColumn1AccountColumn1AccountColumn1IndexAccounts
3
PL410desc of PL410PL430desc of PL430PL460desc of PL460
1​
PL333
4
PL410desc of PL410PL430desc of PL430PL460desc of PL460
2​
PL410
5
PL420desc of PL420PL440desc of PL440PL470desc of PL470
3​
PL420
6
PL420desc of PL420PL440desc of PL440PL480desc of PL480
4​
PL430
7
PL450desc of PL450PL470desc of PL470PL490desc of PL490
5​
PL440
8
PL333desc of PL333
6​
PL450
9
7​
PL460
10
8​
PL470
11
9​
PL480
12
10​
PL490
13
11​

<tbody>
</tbody>

As you can see, the Pivot Table in column L shows every account only once. To be able to do this, I loaded the HelperTable in column J to the data model and created a calculated column called 'Account' with the following code (TableA, TableB, and TableC are the tables you want to summarize)

Code:
=
VAR AppendTables =
    UNION (
        DISTINCT ( TableA[Account] ),
        DISTINCT ( TableB[Account] ),
        DISTINCT ( TableC[Account] )
    )
VAR RemoveDuplicates =
    SUMMARIZE ( AppendTables, [Account] )
VAR AddIndex =
    ADDCOLUMNS (
        RemoveDuplicates,
        "Index", RANKX ( RemoveDuplicates, [Account],, TRUE )
    )
VAR RowIndex = 'HelperTable'[Index]
VAR FilterTable =
    FILTER ( AddIndex, [Index] = RowIndex )
VAR RemoveIndex =
    SUMMARIZE ( FilterTable, [Account] )
RETURN
    IF ( ISBLANK ( RemoveIndex ), "", RemoveIndex )

It uses variables to store the temporary the tables needer to get the expected results. It is probably very far from the optimal formula, but it worked for me. The main steps it follows are:
  • Append the different account tables, stacking them on top of each other.
  • Remove duplicate accounts and extra columns.
  • Add an index column starting at 1.
  • Retrieves the row number from the HelperTable to use as a filter for the temporary table.
  • Filters the temporary table using the row number.
  • Removes the Index column from the temporary table.
  • Checks if the result is empty and turns it into an empty string. If it is not empty, it returns the value.

I hope it helps.
 
Upvote 0
Hi FranzV,

Thanks for the solution.

Let me try and see if I can get it to work.

By any chance, there's a 'thumbs up' etc that increase your reputation? If there is, is it that 'Like'?
 
Upvote 0
Power Pivot in Excel doesn't allow you to add tables using formulas only. You need to import them from a source or paste the data directly into Power Pivot. However, you can get the desired results using a helper table that only serves as a base for other calculations. I created a dummy file that uses a table with consecutive numbers starting at one and should have at least one row for each unique account. If you don't know the max number of accounts, just use a number that is large enough (eg. in the dummy there are 10 distinct accounts but the HelperTable has 1,000 rows, just to be on the safe side). Of course, it is better to keep your table as small as possible to avoid performance issues.

The file looks like:

Excel 2016 (Windows) 64 bit
A
B
C
D
E
F
G
H
I
J
K
L
M

1

2
Account
Column1
Account
Column1
Account
Column1
Index
Accounts

3
PL410
desc of PL410
PL430
desc of PL430
PL460
desc of PL460

1​
PL333

4
PL410
desc of PL410
PL430
desc of PL430
PL460
desc of PL460

2​
PL410

5
PL420
desc of PL420
PL440
desc of PL440
PL470
desc of PL470

3​
PL420

6
PL420
desc of PL420
PL440
desc of PL440
PL480
desc of PL480

4​
PL430

7
PL450
desc of PL450
PL470
desc of PL470
PL490
desc of PL490

5​
PL440

8
PL333
desc of PL333

6​
PL450

9

7​
PL460

10

8​
PL470

11

9​
PL480

12

10​
PL490

13

11​

<tbody>
</tbody>

As you can see, the Pivot Table in column L shows every account only once. To be able to do this, I loaded the HelperTable in column J to the data model and created a calculated column called 'Account' with the following code (TableA, TableB, and TableC are the tables you want to summarize)

Code:
=
VAR AppendTables =
    UNION (
        DISTINCT ( TableA[Account] ),
        DISTINCT ( TableB[Account] ),
        DISTINCT ( TableC[Account] )
    )
VAR RemoveDuplicates =
    SUMMARIZE ( AppendTables, [Account] )
VAR AddIndex =
    ADDCOLUMNS (
        RemoveDuplicates,
        "Index", RANKX ( RemoveDuplicates, [Account],, TRUE )
    )
VAR RowIndex = 'HelperTable'[Index]
VAR FilterTable =
    FILTER ( AddIndex, [Index] = RowIndex )
VAR RemoveIndex =
    SUMMARIZE ( FilterTable, [Account] )
RETURN
    IF ( ISBLANK ( RemoveIndex ), "", RemoveIndex )

It uses variables to store the temporary the tables needer to get the expected results. It is probably very far from the optimal formula, but it worked for me. The main steps it follows are:
  • Append the different account tables, stacking them on top of each other.
  • Remove duplicate accounts and extra columns.
  • Add an index column starting at 1.
  • Retrieves the row number from the HelperTable to use as a filter for the temporary table.
  • Filters the temporary table using the row number.
  • Removes the Index column from the temporary table.
  • Checks if the result is empty and turns it into an empty string. If it is not empty, it returns the value.

I hope it helps.

Hi FranzV,

I tried to do it, but it gave me an error.

Below are the steps that I did.

Created TableA, TableB, TableC and TableD (TableD is the Index).
I load all 4 tables into Data Model by 'Add to Data Model'
In Table4, I added a new column and renamed it to 'Account'.
Just 1 cell below the 'Account', I input the code that you gave me and it gave me an error.

May I know what's wrong with my steps or am I missing something here.
 
Upvote 0
It might be that I didn't name the 4th table TableD but instead HelperTable. You can either try renaming the table to HelperTable or changing the only mention of 'HelperTable' in the formula for 'TableD' (I am not sure if TableD should be enclosed in 'commas')

=
VAR AppendTables =
UNION (
DISTINCT ( TableA[Account] ),
DISTINCT ( TableB[Account] ),
DISTINCT ( TableC[Account] )
)
VAR RemoveDuplicates =
SUMMARIZE ( AppendTables, [Account] )
VAR AddIndex =
ADDCOLUMNS (
RemoveDuplicates,
"Index", RANKX ( RemoveDuplicates, [Account],, TRUE )
)
VAR RowIndex = 'HelperTable'[Index]
VAR FilterTable =
FILTER ( AddIndex, [Index] = RowIndex )
VAR RemoveIndex =
SUMMARIZE ( FilterTable, [Account] )
RETURN
IF ( ISBLANK ( RemoveIndex ), "", RemoveIndex )


If that doesn't work you could try downloading the dummy file and telling us what the error message says.
 
Last edited:
Upvote 0
Hi FranzV,

I still get the same error, doesn't matter if I change 'TableD' which is only consist of 1 column into 'HelperTable' or vice versa.

The error that I get is 'The syntax for 'AppendTables' is incorrect.
The calculated column 'HelperTable[Account]' contains syntaxerror. Provide a valid formula'

Just to re-iterate, below are the steps that I've done.

Below are the steps that I did.

Created TableA, TableB, TableC and HelperTable (HelperTable consist only 1 column, which is the 'Index', and add it to data model. In the data model, I then 'add column' and rename it to 'account').
I load all 4 tables into Data Model by 'Add to Data Model'
In HelperTable, I added a new column and renamed it to 'Account'.
Just 1 cell below the 'Account', I input the code that you gave me and it gave me an error.
When I press 'ENTER', it gave me the above message.

Also, I notice that when I copy and paste the formula on the 'formula bar', I noticed that ISBLANK is highlighted as the code below (yes...the value inside the brackets are bold)...it's like Excel is telling me that I'm missing a bracket...

Code:
ISBLANK([B]VALUE[/B])

Last but not least, when I click enter (either with an extra closed bracket or not), then the message error appears, with the 'AppendTables' being highlighted.




It might be that I didn't name the 4th table TableD but instead HelperTable. You can either try renaming the table to HelperTable or changing the only mention of 'HelperTable' in the formula for 'TableD' (I am not sure if TableD should be enclosed in 'commas')

=
VAR AppendTables =
UNION (
DISTINCT ( TableA[Account] ),
DISTINCT ( TableB[Account] ),
DISTINCT ( TableC[Account] )
)
VAR RemoveDuplicates =
SUMMARIZE ( AppendTables, [Account] )
VAR AddIndex =
ADDCOLUMNS (
RemoveDuplicates,
"Index", RANKX ( RemoveDuplicates, [Account],, TRUE )
)
VAR RowIndex = 'HelperTable'[Index]
VAR FilterTable =
FILTER ( AddIndex, [Index] = RowIndex )
VAR RemoveIndex =
SUMMARIZE ( FilterTable, [Account] )
RETURN
IF ( ISBLANK ( RemoveIndex ), "", RemoveIndex )


If that doesn't work you could try downloading the dummy file and telling us what the error message says.
 
Upvote 0
I believe we need some help from a DAX expert. I know that Matt Allington is an active member of the forum that could surely help in finding the solution, but any DAX expert is welcome to help.

Have you tried using the dummy file?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top