Convert Columns in Data to rows

[Mr_Tidbit]

Hello,

I'm currently working with a dataset that is coming in from one of our project offices excel files. The data has 27 columns, I need to take the last 14 columns and create new rows for those entries and keep the data in the first 15 columns. In excel I would look to use a VBA to alter the data, but I'd prefer to do this on my end in Power BI as to not transform their data and the way they're using it. I've attached screenshots of the queries with the headers.

 

[sandy666]

result example?
more representative than your pictures above

 

[Mr_Tidbit]

Sorry, this is what I'm trying to do with the query, but I have 115 rows coming in from the excel file. So I need to do it for each row.

 

[sandy666]

table cannot contain the same headers

 

[sandy666]

try unpivot last 14 columns
replace eg. Phase1, Phase2 to Phase (and so on with the other Attributes)
Conditional Columns to separate Attributes and their Values
fill down
pivot new Attributes by their Values
filter by null

edit: there will be a problem with null but maybe you will solve it

 

[sandy666]

or you can try this: Convert Columns in Data to rows

 

[Mr_Tidbit]

Thanks for the suggestions. Sorry I got stuck working a different project yesterday and didn't have time to check back in. I'll take a look at your suggestions and see if any of them change the data and I'll let you know. Thanks again.

 

[sandy666]

sure
you can optimise M-code to use in a single Query instead of I posted