Forum Discussion
Split table based on column value, transform, combine back
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.
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.
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?
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.Thank you so much, it worked!
Unpivot Other Columns option did the trick.
