Summarizing one table in a new table

[RvdHeijden]

Goodday,

How can i summarize 1 table into a new table if i want the following result

Mainproject            City

A                             A

A                             A

A                             B

A                             B

B                             A

B                             C

A                             C

B                             A   

A                             A

C                             A

C                             D

the desired outcome should be this (without the colours ofc but with the summarization and grouping)

Mainproject           City

A                              A

A                              B

A                              C

B                              A

B                              C

C                              A

C                              D

I dont want to work with additional colums (if that is possible)

[Zubair_Muhammad]

@RvdHeijden

How about a New Calculated Table

From the Modelling Tab>>New Table

New Table = DISTINCT(Table1)

[RvdHeijden]

@Zubair_Muhammad

Im not sure if that will work because if i have 4 distinct names in the column 'Projectnaam' then it wille return those 4 values in Column A in 4 rows.

In Column B i need the unique City names that are in the Projectname

If for instance Projectname A has 10 different cities in it i need 10 rows with in Column A 10 times the same Projectname and in Column B the 10 unique citynames for that given Projectname.

[Zubair_Muhammad]

@RvdHeijden

It works with your sample data

Could you give me some more data and expected results to work with?

[RvdHeijden]

@Zubair_Muhammad

Its hard to explain but im gonna try none the less.

I now have tabled based on 'City' names and a few calculated colums such as 'total amount of adresses', 'adresses that have status x', 'adresses that have status y' etc etc.

But that is based on the city and now im missing the column for the 'Projectname'.

For example there are 1483 homes in the city of Avenhorn but it's a possibility that 1000 homes are in Project A en 483 are in Project B. I need to be able to make that difference so i need to add that column.

So basically if Avenhorn is in 2 different projects i need to rows for Avenhorn telling me how many homes are in Project A en how many are in Project B.

[RvdHeijden]

@Zubair_Muhammad or anyone else ?

Any ideas ?

[Zubair_Muhammad]

@RvdHeijden

Hi,

Please could you provide some data (that can be copied) and expected results?

[RvdHeijden]

I thought i did that in my last reply @Zubair_Muhammad 😉
I gave you more information and a screenshot of all the cities but im missing the projectnames, they are in a different table.
Is it possible to create a new table with a colum with the projectname and 1 column with the unique cities for each projectname

I can't attach an excel file but it's not that difficult what im asking but let me try again.

I have one table with 40.000 rows in them.

Every row has a City and a ProjectName in them

Both City and Projectnames are NOT unique

A City is always in a Project and a project is always in a city

What i need is the following

A new table to summarize that is Project A has 12000 rows and has 10 unique rows

A summarization of the unique City names per Projectname

So if Projectname A has 12.000 rows in the table but has 4 unique Citynames then the result should be

Project A                 City A

Project A                 City B

Project A                 City C

Project A                 City D

If the next Projectnam has 8000 rows and 2 unique cities, the result should be

Project B                 City D

Project B                 City E

As you can see City D is both in Project A as in Project B and that is what i need

I need a complete summarization (in a new table) of all 40.000 rows

[Zubair_Muhammad]

@RvdHeijden

Please send me your Excel File.

I think Distinct(TableName) should do the job. Because it looks at distinct row...not a particular column

[Zubair_Muhammad]

Hi @RvdHeijden

Please try this calculated table based on the file you sent

Table =
VAR mytable =
    SELECTCOLUMNS ( Blad1, "Hoofdproject", [Hoofdproject], "plaats", [plaats] )
RETURN
    DISTINCT ( mytable )

[Zubair_Muhammad · Accepted solution]

@RvdHeijden

File attached here as well