Split table based on column value, transform, combine back

[itskool]

Hi guys,

I have a task that I can't really wrap my head around:

I have Excel files with a similar structure that I am loading from on folder on Sharepoint and combining them (every month there is a new file).

After that I need to split this one query into multiple (dynamic number since there are files adding every month) based on Source.Name, unpivot columns in all of them and then combine these tables back.

Do you know if something like this is possible in PowerQuery?

I found this solution, but it does not work for me because there are new adding: https://community.powerbi.com/t5/Desktop/Splitting-a-table-into-two-based-on-column-value/m-p/115702

Thanks in advance!

[v-jingzhang]

Hi @itskool 

You don't need to split the combined query and combine them again. When you use Sharepoint Folder connector to connect to the files and combine them, there will be a Transform Sample File query in Helper Queries Folder. You can do transformation steps (unpivot columns...) in this query. This transformation will be applied to all files that are combined. The combined result in Query1 will be updated automatically. 

If Query1 has a "Changed Type" step as the last step, you can remove it. Instead, change column types in Transform Sample File. Sometimes this step in Query1 will make updating combined result not successful. 

Pete has provided a helpful link, you can refer to that blog for details. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[itskool]

Thanks a lot, this is good to know, I was not aware of this functionality.

However, it did not work for me, because part of the transformation I would like to do is to unpivot the columns which have dates in the headers.

So the headers of the columns in files for different months are different and they become the same only after unpivoting. So Query1 throws the following error:

Is there maybe a workaround for that?

[v-jingzhang · Accepted solution]

Hi @itskool 

In Transform Sample File, remove "Changed Type" steps which will refer to columns which are named with dates. Select the common columns and select Unpivot Other Columns option. 

Do other transformation operations after unpivoting. This could avoid referring to a column by date name. 

In the combined query, remove "Changed Type" step too. Only remain steps from the beginning to Expanded Table Column. 

You need to check both Transform Sample File query and Combined query to see if there is any step referring to those date columns by column name. This will cause the error you showed. 

If you don't know how to deal with some steps, you can open Advanced Editor and paste its code here after removing sensitive info. Then we can review the transformation steps and try to modify some steps to overcome this. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[itskool]

Thank you so much, it worked!

Unpivot Other Columns option did the trick.

[BA_Pete]

Hi @itskool ,

It sounds like you want to use the Combine & Transform SharePoint Folder connector.

Just make sure that all of your Excel files, including the new ones each month, go into the same source folder.

Try this:

https://powerbi.microsoft.com/en-us/blog/combining-excel-files-hosted-on-a-sharepoint-folder/ 

Pete