How to Split a Mixed-Type Column into Separate Columns in WPS Power Query
Question details
The user needs to split a Power Query column containing mixed data types into separate columns based on the detected type of each value.
- Product
- WPS Spreadsheets - Power Query
- Device & OS
- not provided
- Scenario
- The user is working in Power Query with an Attribute column whose values have different data types and wants to separate them into type-specific columns for analysis.
- Observed behavior
- A dynamic type-detection column can be added with M code, and the resulting Type column can be used to group, filter, or pivot values into separate columns.
Open the Power Query Editor in WPS Spreadsheets and confirm that the Attribute column you want to split contains values with different data types. Ensure you can access the Advanced Editor or a custom column step where M code can be added.
Add a Dynamic Type-Detection Column and Split Mixed-Type Values in WPS Power Query
Use this method when an Attribute column contains values of different data types and you need to group, filter, or pivot them into separate type-specific columns.
Power Query can identify the data type of each value by using Value.Type and Table.Schema. The Type column remains dynamic because it is calculated from the current values instead of relying on a manually maintained list of supported types.
In WPS Spreadsheets, open the Power Query Editor from the Data tab and select the query that contains the mixed-type Attribute column.
In the Power Query Editor, open the Advanced Editor or use Add Column > Custom Column to insert a new M expression step after the previous step, referred to here as PrevStep.
Add a column named Type with the formula: addType = Table.AddColumn(PrevStep, "Type", each Table.Schema(#table(type table [a = Value.Type(_[Attribute])], {}))[Kind]{0}, type text). This returns the data type of each Attribute value as text.
With the new Type column, use Group By, Filter Rows, or Pivot Column in the Transform tab to move each detected type into its own column or row set.
Split Mixed-Type Power Query Columns with WPS Office
Use WPS Spreadsheets Power Query to add a dynamic type-detection column, then pivot or filter the results into separate type-specific columns without losing data.
- 1. Open WPS Spreadsheets and load the mixed-type query: Launch WPS Office Spreadsheets, click the Data tab, and open Power Query Editor for the workbook that contains the Attribute column.
- 2. Add the dynamic Type column: Insert an M step using Table.AddColumn with Value.Type and Table.Schema to create a Type column that returns each value's data type as text.
- 3. Pivot or filter the Type column: Use Transform > Pivot Column or Group By to split the detected types into separate columns based on your report layout.
- 4. Close and Load the result: Click Close & Load To in the Power Query Editor to write the transformed table back to a worksheet in WPS Spreadsheets.
Frequently Asked Questions
Can WPS Power Query handle mixed data types in the same column?
Yes. You can add a calculated Type column with Value.Type and Table.Schema M code, then use that column to pivot or filter so each value is handled according to its detected type.
What M expression returns the data type of a value in Power Query?
Use Value.Type(_[Attribute]) inside Table.AddColumn. To return a friendly type name, wrap it with Table.Schema(#table(type table [a = Value.Type(_[Attribute])], {}))[Kind]{0} and set the column type to text.
Does the type detection stay dynamic if the data changes?
Yes. Because the Type column is calculated from each row's value, refreshing the query re-evaluates value types. It does not depend on a fixed list of supported types.
How do I split the detected types into separate columns?
After adding the Type column, use the Power Query Transform tab to Pivot Column on the Type field, or use Group By and Filter to arrange each type into its own column.
Which WPS entry point opens Power Query?
In WPS Spreadsheets, go to the Data tab and use the Power Query or Get Data options to open the Power Query Editor, then access your query steps.
