Forum Discussion
Unpivoting clustered columns
I'm seeing this part there:
Would it break that part, or something else, if the V columns sometimes have a trailing character like ":"?
Like this:
Like, the source is data from a SharePoint form and they messed up the back big time. They just expect me to do magic or something.
Right now it is splitting the column where it transitions from letter to number.
If there are trailing characters after the number it will 'break' the grouping step that follows.
It sounds like you may need to add a step (or two) after the split to clean the category column to allow for the grouping to occur correctly.
The nasty thing with this is that it is a simplification as well. There are 40 columns per category and no guarantee that the field name remain the same :). I forsee a lot of trouble during the scheduled refreshes...
Like, as well, I don't understand how your code works, nor the one from community support. How am I supposed to trouble shoot something I don't understand? Like, I have sufficient experience with Power BI to have a rough understanding what it does so I can cherry pick of a few uncertainties, but that's it.
I think the concatenation solution from j_ocean might be the best as it is the simplest and thereby guarantees for me continuity. Like, I'll wait a bit for your response. Perhaps your code is really easy to understand and trouble shoot, but otherwise I'll mark the merge of the column and after unpivot splitting them again as the solution.
The basic premise of the solution I provided was that it takes the existing columns that need to be split and turns them into rows with their coresponding values. (The unpivot step.) From there the critical piece of the solution is having a consistent method of splitting the 'Category' column into the two columns you desire. This step can be as simple as the example where you split by transition from letter to number or it can be a complex, multi step process that uses conditional logic to split the column.
Once the 'Category' column has been split my solution groups the rows based on the 'Id' and resulting 'Category' column. (Grouped Rows step)The 'Custom1' step works on the inner tables that were created in the 'Grouped Rows' step. In this step I select the second split category ('Category.2') and the 'Value' columns from each of the inner tables and then pivots those two columns so that they can be expanded back into the main table.
The 'Expanded _grouped' step does just that.
Again, the critical piece of this solution is having logic that will split your column headers into the two columns you desire. The rest of the solution is really just table manipulation. As an aside, the solution from wdx223_Daniel does all of this in one line. It is a brillant piece of code.
Hope this helps a bit.