0

I tried classic formulation of CONCATENATEX, but it didnt work.

I need calculate this table 

ID  TYPE
1   A
1   B
2   B
3   A
4   A
4   A
4   A
4   C
4   D
4   E
5   B
5   B
6   A
7   A
7   B
7   C
8   B
8   B
9   D
10  A
10  A
10  D

to this table 

ID  TYPES
1   A,B
2   B
3   A
4   A,A,A,C,D,E
5   B,B
6   A
7   A,B,C
8   B,B
9   D
10  A,A,D

Looked for answer in exceltown, didnt help.

kombi = CONCTENATEX(TABLE;TYPE;"+")

I expect result A+B or A+A+A, or A+C, but results be like

A+A+A+A+A+B+B+B+B+B+C+C+C+C+B+B+B+B+B+A+A+A+A++D+D+D+

enter image description here

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
Jan Maštálka
  • 7
  • 4
  • You can refer this answer https://stackoverflow.com/questions/44058355/powerquery-how-can-i-concatenate-grouped-values – Prem Chand Aug 16 '19 at 09:15
  • Your formula is perfectly fine, the visual in which you evaluate it causes your problem. I assume you are trying a table visual with one column ID and another the measure [kombi]? Make sure ID is set to the aggregation 'don't summarize'. It will result in a list of IDs and the proper evaluation of your measure. By default, it is usually is summed as ID often get recognized as integers. – Jelle Hoekstra Aug 16 '19 at 11:35

1 Answers1

0

Step 1 -> Screenshot 1 Step 2 -> Screenshot 2

Step 3 -> Screenshot 3

You can use the below power query to get the desired result.

let
    Source = Excel.Workbook(File.Contents("c:\Desktop\stac.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"TYPE", type text}, {"ID", type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"ID", type text}, {"TYPE", type text}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type", {"ID"}, {{"All Rows", each _, type table [ID=text, TYPE=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Custom", each [All Rows][TYPE]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "+"), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"All Rows"})
in
    #"Removed Columns"
Prem Chand
  • 126
  • 1
  • 10
  • I am sorry I forgot to write that I need it in Power BI , excel cant process big data set. (file has about 200MB in excel) – Jan Maštálka Aug 16 '19 at 10:23
  • When you open the power bi you will be having edit query option, where you can write power query(M Query) to do data manipulation. In power query you can paste this query using advance editor option and change only the source file as mentioned – Prem Chand Aug 16 '19 at 11:49
  • I have added step file screenshot to do it in powerbi – Prem Chand Aug 16 '19 at 12:38
  • It works, I am sorry I need 10 more reputation to give you upvote, but you can expect it. Im curious How can I add DISTINCT: For example row 4 from picture is 4 - A,A,A,C,D,E i want to transform it to 4 - A,C,D,E – Jan Maštálka Oct 09 '19 at 07:38