Creating new table in MS Excel 2013 PowerPivot

[dluhut]

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.

 

[dluhut]

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?

I have created a dummy/sample file, but where can I download to, as I can't put it as an attachment here.

 

[FranzV]

I was refering to the dummy file I created and uploaded to Dropbox that you can download using this link. After opening it, go into Powerpivot to check if it works properly.

If you want to upload your file try using Dropbox. It's easy.

 

[dluhut]

Hi FranzV,

I tried download your and when look at it, it's all #ERROR.I
As well, when I delete the formula and copy back, it's giving me the same error.

I'm wondering if it's because of my PowerPivot? I'm using MS Excel 2013

The dropbox link is https://www.dropbox.com/s/53347uvb65vb7vk/PLAccount.xlsx?dl=0

I was refering to the dummy file I created and uploaded to Dropbox that you can download using this link. After opening it, go into Powerpivot to check if it works properly.

If you want to upload your file try using Dropbox. It's easy.

 

[FranzV]

I'm wondering if it's because of my PowerPivot? I'm using MS Excel 2013

I am sorry to have insisted so much in using my approach before checking its compatibility with your Excel version. Indeed, variables were introduced to DAX in may 2015. You can read about it on this article by The Italians, whose site is one of the best references for DAX. A workaround is nesting each variable's code into the next, resulting in the following code:

HelperTable[Account] =
SUMMARIZE (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                UNION (
                    DISTINCT ( TableA[Account] ),
                    DISTINCT ( TableB[Account] ),
                    DISTINCT ( TableC[Account] )
                ),
                [Account]
            ),
            "Index", RANKX (
                SUMMARIZE (
                    UNION (
                        DISTINCT ( TableA[Account] ),
                        DISTINCT ( TableB[Account] ),
                        DISTINCT ( TableC[Account] )
                    ),
                    [Account]
                ),
                [Account],
                ,
                TRUE
            )
        ),
        [Index] = 'HelperTable'[Index]
    ),
    [Account]
)

Which works for Excel 2016 as well. Now the only problem is the UNION function that was also added in the 2016 version. Jason Thomas wrote an article on simulating the UNION operation in Excel 2013 that might be of use.

 

[FranzV]

I tried using Jason Thomas's UNION method and after some tweaking I got it right. I updated the Dropbox dummy with the new formula. The code is much longer, but it should work in Excel 2013. Please let me know if it did.

=SUMMARIZE (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                ADDCOLUMNS (
                    CROSSJOIN (
                        ADDCOLUMNS (
                            SUMMARIZE ( ADDCOLUMNS ( TableA, "A1", [Account] ), [A1] ),
                            "B1", RANKX (
                                SUMMARIZE ( ADDCOLUMNS ( TableA, "A1", [Account] ), [A1] ),
                                [A1],
                                ,
                                1,
                                DENSE
                            )
                        ),
                        ADDCOLUMNS (
                            SUMMARIZE ( ADDCOLUMNS ( TableB, "A2", [Account] ), [A2] ),
                            "B2", RANKX (
                                SUMMARIZE ( ADDCOLUMNS ( TableB, "A2", [Account] ), [A2] ),
                                [A2],
                                ,
                                1,
                                DENSE
                            )
                        ),
                        ADDCOLUMNS (
                            SUMMARIZE ( ADDCOLUMNS ( TableC, "A3", [Account] ), [A3] ),
                            "B3", RANKX (
                                SUMMARIZE ( ADDCOLUMNS ( TableC, "A3", [Account] ), [A3] ),
                                [A3],
                                ,
                                1,
                                DENSE
                            )
                        )
                    ),
                    "Account", SWITCH ( TRUE (), [B3] = 1, [A2], [B2] = 1, [A1], [A3] )
                ),
                [Account]
            ),
            "Index", RANKX (
                SUMMARIZE (
                    ADDCOLUMNS (
                        CROSSJOIN (
                            ADDCOLUMNS (
                                SUMMARIZE ( ADDCOLUMNS ( TableA, "A1", [Account] ), [A1] ),
                                "B1", RANKX (
                                    SUMMARIZE ( ADDCOLUMNS ( TableA, "A1", [Account] ), [A1] ),
                                    [A1],
                                    ,
                                    1,
                                    DENSE
                                )
                            ),
                            ADDCOLUMNS (
                                SUMMARIZE ( ADDCOLUMNS ( TableB, "A2", [Account] ), [A2] ),
                                "B2", RANKX (
                                    SUMMARIZE ( ADDCOLUMNS ( TableB, "A2", [Account] ), [A2] ),
                                    [A2],
                                    ,
                                    1,
                                    DENSE
                                )
                            ),
                            ADDCOLUMNS (
                                SUMMARIZE ( ADDCOLUMNS ( TableC, "A3", [Account] ), [A3] ),
                                "B3", RANKX (
                                    SUMMARIZE ( ADDCOLUMNS ( TableC, "A3", [Account] ), [A3] ),
                                    [A3],
                                    ,
                                    1,
                                    DENSE
                                )
                            )
                        ),
                        "Account", SWITCH ( TRUE (), [B3] = 1, [A2], [B2] = 1, [A1], [A3] )
                    ),
                    [Account]
                ),
                [Account],
                ,
                1
            )
        ),
        [Index] = 'HelperTable'[Index]
    ),
    [Account]
)

 

[dluhut]

Beautiful!!!

It works FranzV!!!

Another like again for you!!!

I tried using Jason Thomas's UNION method and after some tweaking I got it right. I updated the Dropbox dummy with the new formula. The code is much longer, but it should work in Excel 2013. Please let me know if it did.

=SUMMARIZE (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                ADDCOLUMNS (
                    CROSSJOIN (
                        ADDCOLUMNS (
                            SUMMARIZE ( ADDCOLUMNS ( TableA, "A1", [Account] ), [A1] ),
                            "B1", RANKX (
                                SUMMARIZE ( ADDCOLUMNS ( TableA, "A1", [Account] ), [A1] ),
                                [A1],
                                ,
                                1,
                                DENSE
                            )
                        ),
                        ADDCOLUMNS (
                            SUMMARIZE ( ADDCOLUMNS ( TableB, "A2", [Account] ), [A2] ),
                            "B2", RANKX (
                                SUMMARIZE ( ADDCOLUMNS ( TableB, "A2", [Account] ), [A2] ),
                                [A2],
                                ,
                                1,
                                DENSE
                            )
                        ),
                        ADDCOLUMNS (
                            SUMMARIZE ( ADDCOLUMNS ( TableC, "A3", [Account] ), [A3] ),
                            "B3", RANKX (
                                SUMMARIZE ( ADDCOLUMNS ( TableC, "A3", [Account] ), [A3] ),
                                [A3],
                                ,
                                1,
                                DENSE
                            )
                        )
                    ),
                    "Account", SWITCH ( TRUE (), [B3] = 1, [A2], [B2] = 1, [A1], [A3] )
                ),
                [Account]
            ),
            "Index", RANKX (
                SUMMARIZE (
                    ADDCOLUMNS (
                        CROSSJOIN (
                            ADDCOLUMNS (
                                SUMMARIZE ( ADDCOLUMNS ( TableA, "A1", [Account] ), [A1] ),
                                "B1", RANKX (
                                    SUMMARIZE ( ADDCOLUMNS ( TableA, "A1", [Account] ), [A1] ),
                                    [A1],
                                    ,
                                    1,
                                    DENSE
                                )
                            ),
                            ADDCOLUMNS (
                                SUMMARIZE ( ADDCOLUMNS ( TableB, "A2", [Account] ), [A2] ),
                                "B2", RANKX (
                                    SUMMARIZE ( ADDCOLUMNS ( TableB, "A2", [Account] ), [A2] ),
                                    [A2],
                                    ,
                                    1,
                                    DENSE
                                )
                            ),
                            ADDCOLUMNS (
                                SUMMARIZE ( ADDCOLUMNS ( TableC, "A3", [Account] ), [A3] ),
                                "B3", RANKX (
                                    SUMMARIZE ( ADDCOLUMNS ( TableC, "A3", [Account] ), [A3] ),
                                    [A3],
                                    ,
                                    1,
                                    DENSE
                                )
                            )
                        ),
                        "Account", SWITCH ( TRUE (), [B3] = 1, [A2], [B2] = 1, [A1], [A3] )
                    ),
                    [Account]
                ),
                [Account],
                ,
                1
            )
        ),
        [Index] = 'HelperTable'[Index]
    ),
    [Account]
)

 

[FranzV]

I'm glad to have helped. I have to admit it was quite challenging and an excellent opportunity to practice table operations I usually do with Power Query.