Transformer different rows of 1 column into multi column

[Anonymous]

Hi folks,

A have a Excel Q&A Database with multiple questions and answers in differente sheets from diffentes people/companies, as example in below link to download from "mega.nz".

• https://mega.nz/file/xJMXSaDQ#CL6YIJgoE2OSDFYbm5IVHRyZn6MxoRUFckydplxtsYk 

The ideia is get these diffente sheets and import to PowerBI into a single table (append all queryes for each question/sheet) with the structure as created in the Excel file.

I got some results in the sheet that I have just 1 Master Questions, Questions, answers, removing the rowns until the last questions, and make a pivot table, but when I have 3 levels of questions, its won't work because i had many steps to do manually.

It's possible automate one query to read each table and import the data as exemple in the file? If yes, How could I "automate" this query?

Thanks.

#besafe #behealthy

[camargos88]

Hi @Anonymous ,

It's possible, you need to use Folder connector and apply all transformations on the file you used as example. So, it will be applied to all tables and append later.

[Anonymous]

Hi @camargos88 ,

Unfortully, I don't know how to make this tranformation in the rows for each Question/Subquestion/optiond in the columns, and create the coloumns  in the sample file/table to applie in other queries.

[camargos88]

Hi @Anonymous ,

Can you provide some file sample, with dummy data ? 

So I can replicate it from my side with multiples files.

[Anonymous]

Hi...

Sample file data is shared in the link in my post.

https://mega.nz/file/xJMXSaDQ#CL6YIJgoE2OSDFYbm5IVHRyZn6MxoRUFckydplxtsYk 

[camargos88 · Accepted solution]

Hi @Anonymous ,

Check this file: Download PBIX 

[Greg_Deckler]

Not entirely sure but it sounds like you want a Folder query.

[Anonymous]

Actualy, I have just one file (as example) but with many worksheet, and inside them, I have the questions/answers.
I will need probally at least one query for each worksheet, but I don't know how to "broke" the lines of the questions/options into columns (Master question / Question / Options).

BR.