1. I have a list of unique IDs along with its data point.

2. I am grouping by the unique IDs then adding a custom column to get a list of those data points

   = Table.AddColumn(#"Grouped Rows", "Base Salary Data.1", each Table.Column([Base Salary Data],"Base (location adj)"))

3. From here I am concatenating each data point with the '@' symbol and I also have a column with the count of data points per unique ID (nData).

4. I am using the max of that 'nData' to then determine the number of columns needed to split out the concatenated data points

   = List.Max(ReadyToSplit[nData])

5. So from here everything works great, except the newly created columns are auto assigned to text and I need them in decimal number data type. I've tried just converting all the columns to decimal number as another step, but then the last column is hard-coded into the formula, "Base Salary Data.1.500" in this case. So if the max nData is changed to be "Base Salary Data.1.499" by removing data in the raw dataset, the error screenshotted below is given.

Error after removing the 500th "Base Salary Data.1.500

CodePudding user response：

This converts any column that contains Base Salary Data in the column name to a number type

#"ChangeFormat" = Table.TransformColumnTypes(#"PriorStepNameHere",List.Transform(List.Select(Table.ColumnNames(#"PriorStepNameHere"), each Text.Contains(_, "Base Salary Data")),each {_, type number}))