Merging Rows & Columns into single Row

[Daniel_B]

Hi All

I have a somewhat complex question (at least of me) and I'm not even really sure how what is the best question to ask when searching Google for help

I have 12 .CSV files (for each month) which have all of my review data which has review IDs as the row and then columns with headers and the answers below them for each review

The problem with my data is that each answer to a question is a separate row for the same ID which really should be a single row of all the answers but the report I receive is just created that way in SQL and sent to me (I do not have the authority nor ability to amend anything in SQL)

Is there a way to merge those rows into 1 and keep the answers under their respective columns as analysing the data isn't working as the FILTERS cannot average the scores for some of the columns because there isn't a relationship/value to actually calculate

Thanks

Dan_B

[v-easonf-msft]

Hi,  @Daniel_B 

According to your description, I do not quite understand your question.

Could you please share your PBIX/some sample data and expected result to me if you don't have any Confidential Information?

You can upload your pbix file to OneDrive and share it with me if it is convenient..

Remember that you should remove security and sensitive data within the report.

Best regards

Eason 

[Daniel_B]

I've tried this method

1) Check column ID -> Mouse rightclick -> Unpivot Other Columns: This will delete the nulls

2) Check column Attribute -> Transform -> Any Column -> Pivot Column: Choose "Value" in Values Column

and turned off aggregation but I end up with errors and I am not certain that the data has merged correctly as the number of review_IDs is much higher in Power BI than when I remove duplicates from the source file