Pivot, unpivot

[evolu6ion]

Hello,

I have an excel file that looks like that:

SurveyID

Column1

Column2

Which are available?

For how long unavailable?

Next delivery?

Comments?

Which are available?

For how long unavailable?

Next delivery?

Comments?

CreatedOn

Editor

1

Specific information

Available information

x1,x2,x3

some comments

3m-6m

21.02.2018

01.01.2018

x

2

Specific information

Available information

24-48h

21.02.2018

x1,x2,x3

some specific comments

02.01.2018

y

3

Specific information

Available information

1w-1m

22.03.2018

some other comments

x1

03.01.2018

u

And I'm trying to get to that:

SurveyID	Column1	Column2	Which are available?	For how long unavailable?	Next delivery?	Comments?	CreatedOn	Editor
1	Specific information	Available information	x1	null	null	some comments	01.01.2018	x
1	Specific information	Available information	x2	null	null	some comments	01.01.2018	x
1	Specific information	Available information	x3	null	null	some comments	01.01.2018	x
1	Specific information	Available information	null	3m-6m	21.02.2018		01.01.2018	x
2	Specific information	Available information	null	24-48h	21.02.2018		02.01.2018	y
2	Specific information	Available information	x1	null	null	some specific comments	02.01.2018	y
2	Specific information	Available information	x2	null	null	some specific comments	02.01.2018	y
2	Specific information	Available information	x3	null	null	some specific comments	02.01.2018	y
3	Specific information	Available information		1w-1m	22.02.2018	some other comments	03.01.2018	u
3	Specific information	Available information	x1	null	null	null	03.01.2018	u

So far in Power Query, I manage to get to that:

But when I try to use this method from this thread (https://community.powerbi.com/t5/Desktop/Unpivoting/m-p/443423#M204928), I get an error message :

I have no idea on how to solve this issue.

Your help will be greatly appreciated, and do not hesitate to correct me or give any advice on how to do this more efficiently.

Thanks in advance!

N.

[v-danhe-msft]

Hi @evolu6ion,

Based on your desired table, I could not figure out how could you get [Which are available?] column?

It seemd you need to split the two [Which are available?] columns in your row table?

Regards,

Daniel He 

[evolu6ion]

Hi @v-danhe-msft 

Sorry for the delay of my feedback and thanks for your answer. Actually, we found a solution using modulo. Because in the original data, the [Which are available] and subsequent columns repeat for more than 180 times. Each time these 4 questions are repeated with different answers given, and each answers may contain multiple items that need to be splitted in order to connect to the original "product" table. When I got more time, I'll answer my thread with the aforementioned solution using modulo.

Regards.