Hi Mynda
I've received a file that is set out in the worst possible way to try and collate and analyse the data. I've had a go at transposing, pivoting, unpivoting, even splitting the query into two different queries then appending back together all to no avail. Maybe this is straightforward (I've found un-pivoting data beyond a couple of levels of grouping challenging) but this seems it would need 10th dimensional data manipulation skills! Not asking you to do my work for me but I'd be interested to know if you thought this particular form could be transformed in Power Query. Thank you.
Kind regards
Tim
Hi Tim,
Please see example file attached. Hope that points you in the right direction.
Mynda
Hi Mynda
That's brilliant! Just one further question. I noticed that you had converted the original form into a table and then split the table into two queries before appending after doing the transformations on each query. How would I go about achieving this if I was combining multiple files from a folder? Would you be able to create a sample file by splitting the data into two queries as you have done and would there be a way to convert the data to a table?
Thanks again
Tim
Hi Tim,
Combine the files from a folder twice, once for the headers and once for the data. Transformations will have to be done in the sample files for each set of queries.
Mynda
Many thanks Mynda.
Hi Mynda
Just following on from the previous post, I've done two folder combines as you suggested and did the separate transformations on each sample file. The output of the queries from both these sample files is fine. I have then tried appending sample file 1 and sample file 2 together. This works fine at the sample file stage but then the resulting query from this appended sample file results in all the data from sample file 1 being correctly populated but the data from sample file 2 just returns null values. Is there anything I'm doing incorrectly here? It should be noted that the data quality on the insurance fields (sample file 2) is poor as the source data did not have data validation in place and thus we have fields that have been filled in all sorts of data types. However, despite this, the second query does return all these values when run separately.
I have been able to get the result I need by loading both queries to Excel then copying and pasting together but it would be good nice to be able to complete the process and have the query update as new data comes in.
Thanks again
Tim
Hi Tim,
You don't append the sample file queries. You append the final queries for each. The sample file only gets one file, the final query gets data from all files. These are the ones you want to append.
Mynda
