Forum Discussion
Helper IPowerBI Table.SplitColumn using conditional length of ranges
I have one column that I wish to split into two.
First column always has the first two characters.
Second column will start with the first character of the original column field based upon Condition A, and the third character when Condition A is not met.
This formula is the base (and works):
= Table.SplitColumn(#"Renamed Columns", "Classification Group C", Splitter.SplitTextByRanges({{0,2},{2,Text.Length("Classification Group C")}}), {"Classification Group C.1", "Classification Group C.2"})
Of course the second column always starts at the third position, and not what I want.
However I am unable to create a condition for the "2" in the second range.
Here is a kludge attempt at this:
= Table.SplitColumn(#"Renamed Columns", "Classification Group C", Splitter.SplitTextByRanges({{0,2},{each if Text.EndsWith ([Classification Group C] ,"Hide") then 0 else 2, Text.Length("Classification Group C")}}), {"Classification Group C.1", "Classification Group C.2"})
In this example an Expression.Error: We cannot convert a vlaue of type Function to type Number. the "each" qualifier, if presented there or before the "Splitter" presents errors ("We cannot apply field access to the type Text").
I can perform this using a non Split Column approach (creating one new column with 2 characters, and another with a condition), but there should be a way to use the Split Column method for this as it "strips away" the original column.
10 Replies
- MarcelBeug
Community Champion
My suggestion would be to add a new column with the conditional part and then transform the original column and take the first 2 positions.
I think you can manage this yourself, otherwise just let us know and we can help you further.
In any case you will end up with an additional column, whether you split the original column or add one yourself.
CLEARIFYYes, I was able to do that, per my original statement, but would be nice to do this in one command .e.g. step I was hoping that the bright people out here (like you), that are much smarter than me, might see something I am missing. Doesn't seem like this should be so hard to do - and might help others down the line too.
But thanks for the reply, much appreciated.
- MarcelBeug
In general it is not good Power Query practice to try and combine multiple actions in 1 step.
You should always bare in mind that the entire query will be evaluated - basically as 1 unit - resulting in some kind of invisible background executable that will create the end result.
In general it is just unknown how that invisible background executable would look like, but it may be very different from the logic as specified in the Power Query code.
However, in case of Query Folding, you can actually view the SQL-code that is generated after each step, showing that all steps - up until and including the current step - are converted into 1 combined SQL-statement.
Back to this particular case: I'm not sure, but I wouldn't be surprised if it is just not possible to use Table.SplitColumn with information from other columns, similarly to Table.TransformColumns.
Be careful with "bright people out here (like you) how are much smarter than me". It is not well appreciated.
Hi CLEARIFY,
I think you can add custom column to add separator to specific index of original text characters, then you can use split column function to deal with this data.
Regards,
Xiaoxin Sheng
- CLEARIFY
Thank you for both responses, but I think you guys are missing my bottom line question. I can already create the columns I need, where a "third" column exists; very easy to do.
But my little pea brain tells me that there should be a way to do this is one step where only two resulting columns exist using the split column formula.
