Using DAX formular in Excel Pivtable

[peter8848]

Hi,

I have a data range with 4 columns but 80k rows. they are all text values. I would like to create a report like the below by using dax formular in data table pivot table "concatenatex "

Data Report
	a	c	b	d
1​	aa	aaa	aaaa	aaaaa
2​	bb	bbb	bbbb	bbbbb
3​	cc	ccc	cccc	ccccc
4​	dd	aaa	aaaa	ddddd
5​	ee	bbb	bbbb	eeeee
Report	Pivot Table
	a	c	b	d
1​		aaaa	bbbb	cccc
2​	aa	aaaaa
3​	bb		bbbbb
4​	cc			ccccc
5​	dd	ddddd
6​	ee		eeeee

However, using the youtube link above I can only select data report for 5000 rows and the data table pivot table will show "reading data" and end up with nothing after. Does anyone know why this is the case or Dax formular has got a limit of the rows for its data sheet?

thanks a lot

Cheers,

Peter

 

[GraH]

Powerpivot has nearly no limit on amount of rows (it depends on your Office version 32bit or 64bit and the amount of available memory). It was build for big data, so I doubt that is the reason.

Your data can best be unpivotted first, that's a small job for PQ. So you'd have more rows, but only 1 measure to create.

I'm just wondering if the concatenation is not creating a too long string for Excel to display? So from your small sample I made a big one so the table holds over 7 Mio rows. And it still worked, though my screen became unreadable.

Maybe check if your Office version is a 32bit or a 64bit.

 

[peter8848]

Powerpivot has nearly no limit on amount of rows (it depends on your Office version 32bit or 64bit and the amount of available memory). It was build for big data, so I doubt that is the reason.

Your data can best be unpivotted first, that's a small job for PQ. So you'd have more rows, but only 1 measure to create.

I'm just wondering if the concatenation is not creating a too long string for Excel to display? So from your small sample I made a big one so the table holds over 7 Mio rows. And it still worked, though my screen became unreadable.

Maybe check if your Office version is a 32bit or a 64bit.

Hi GraH,

Thanks for your reply. It works from row 1 to row 5000 but not for row 1 to row 6000. However if I use from Row 3500 (made it same as title like row 1 in the previous example) to 7000 it still works. It looks like the attachment again when i select row 3500 to 9000. very strange..... Any help please.

Cheers,

Peter

 

[GraH]

Looks like the data is calculated but the pivot can't display the result. I believe the sequence is reading data then populating fields or something of the sorts.
How long are your text fields?
And maybe you can do this easier in Power Query. Then the field also gets truncated at the max length a cell can take. I'm guessing here, since I never use concatenation in PP.
Can you share some realistic, sanitized data via XL2BB?

 

[peter8848]

Looks like the data is calculated but the pivot can't display the result. I believe the sequence is reading data then populating fields or something of the sorts.
How long are your text fields?
And maybe you can do this easier in Power Query. Then the field also gets truncated at the max length a cell can take. I'm guessing here, since I never use concatenation in PP.
Can you share some realistic, sanitized data via XL2BB?

Hi GraH,

Thanks for your reply. The longest text in len() will be more than 40 so I am really not sure it would be a length issue. How would you do it from Power Query?

Cheers,

Peter

 

[peter8848]

Hi GraH,

Thanks for your reply. The longest text in len() will be more than 40 so I am really not sure it would be a length issue. How would you do it from Power Query?

Cheers,

Peter

Also what is XL2BB, sorry the real data is around 80MB so i guess it is hard to share? thanks

 

[GraH]

40 is nothing... As length. Hmm, out of clues here.
See link in my signature for xl2bb.
In PQ you group on your dimension 1, 2,3
,4,5,.. adding aggregate allrows. Then do something like text.combine(table.column([All], "text col"), ",").
Without Excel for the moment. Can have a look later and make a demo with your sample.

 

[peter8848]

40 is nothing... As length. Hmm, out of clues here.
See link in my signature for xl2bb.
In PQ you group on your dimension 1, 2,3
,4,5,.. adding aggregate allrows. Then do something like text.combine(table.column([All], "text col"), ",").
Without Excel for the moment. Can have a look later and make a demo with your sample.

thanks mate. So do i still do it in excel right with the power query?

Cheers,

Peter

 

[GraH]

Yeah, for sure.

Hoping this is the wanted result, because I did not get it from your first post how to make the expected result.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2		Item	a	c	b	d			Item	Text
3		1	aa	aaa	aaaa	aaaaa			1	aa,aaa,aaaa,aaaaa
4		2	bb	bbb	bbbb	bbbbb			2	bb,bbb,bbbb,bbbbb
5		3	cc	ccc	cccc	ccccc			3	cc,ccc,cccc,ccccc
6		4	dd	aaa	aaaa	ddddd			4	dd,aaa,aaaa,ddddd
7		5	ee	bbb	bbbb	eeeee			5	ee,bbb,bbbb,eeeee
8
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"a", type text}, {"c", type text}, {"b", type text}, {"d", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Item"}, {{"All", each _, type table [Item=text, Attribute=text, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Text", each Text.Combine(List.Distinct([All][Value]), ","))
in
    #"Added Custom"