Create a new table starting from existing data

[gcaserta]

Hi to all

I have 2 tables with this structures:

Table 1

Column A: Employee  emails 

Column B: Code of Primary Offices

For this table I can have for different emails the same Code of Primary Company

Example

Employee  email A        Code of Primary Offices A

Employee  email B        Code of Primary Offices A

Employee  email C        Code of Primary Offices B

Employee  email D        Code of Primary Offices B

Table 2

Column A: Code of Primary Office

Column B: Code of Secondary Office

For this table I can have for the same Code Of Primary Office different Code of Secondary Company

Example:

Code Of Primary Office A       Code Of Secondary Office B

Code Of Primary Office A       Code Of Secondary Office C

Code Of Primary Office A       Code Of Secondary Office D

What I need to have and I ask your help is a Table with this structure:

Table New

Employee  email A        Code Of Primary Office A       Code Of Secondary Office B      Code Of Secondary Office C ......

Many thanks for your support           

[gcaserta]

[v-zhangti · Accepted solution]

Hi, @gcaserta 

You can try the following methods.

Column:

Secondary Office Code 1 = 
CALCULATE (
    MIN ( 'Table 2'[Secondary Office Code] ),
    FILTER (
        'Table 2',
        [Primary Office Code] = EARLIER ( 'Table 1'[Primary Office Code] )
    )
)

Secondary Office Code 2 = 
CALCULATE (
    MIN ( 'Table 2'[Secondary Office Code] ),
    FILTER (
        'Table 2',
        [Primary Office Code] = EARLIER ( 'Table 1'[Primary Office Code] )
            && [Secondary Office Code] <> EARLIER ( 'Table 1'[Secondary Office Code 1] )
    )
)

Secondary Office Code 3 = 
CALCULATE (
    MIN ( 'Table 2'[Secondary Office Code] ),
    FILTER (
        'Table 2',
        [Primary Office Code] = EARLIER ( 'Table 1'[Primary Office Code] )
            && [Secondary Office Code] <> EARLIER ( 'Table 1'[Secondary Office Code 1] )
            && [Secondary Office Code] <> EARLIER ( 'Table 1'[Secondary Office Code 2] )
    )
)

Table Result 1:

New Table:

Table Result 2 = 
UNION (
    SUMMARIZE ( 'Table 1', [Email], 'Table 1'[Primary Office Code] ),
    SUMMARIZE ( 'Table 1', [Email], 'Table 1'[Secondary Office Code 1] ),
    SUMMARIZE ( 'Table 1', [Email], 'Table 1'[Secondary Office Code 2] ),
    SUMMARIZE ( 'Table 1', [Email], 'Table 1'[Secondary Office Code 3] )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gcaserta]

@amitchandak Hi replied with tables but i'm not sure if you can see them.

[gcaserta]

Hi Amitchandak

Below the sample. Honestly I didn't find how to send as attachment the files

Thanks 

Table 1

Email	Primary Office Code
john@test1.com	EAXXX
smith@test2.com	EAXXX
frank@test3.com	EBYYY
michael@test4.com	ECZZZ
boris@test5.com	ECZZZ

Table 2

Primary Office Code	Secondary Office Code
EAXXX	EAXX1
EAXXX	EAXX2
EAXXX	EAXX3
ECZZZ	ECZZ1
ECZZZ	ECZZ2

The result:

john@test1.com	EAXXX	EAXX1	EAXX2	EAXX3
smith@test2.com	EAXXX	EAXX1	EAXX2	EAXX3
frank@test3.com	EBYYY
michael@test4.com	ECZZZ	ECZZ1	ECZZ2	ECZZ3
boris@test5.com	ECZZZ	ECZZ1	ECZZ2	ECZZ3

[amitchandak]

@gcaserta ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.