PowerBI Table.SplitColumn using conditional length of ranges

[CLEARIFY]

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.

[v-shex-msft]

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.

[MarcelBeug]

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.

[CLEARIFY]

Yes, 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.

[CLEARIFY]

My apologies if you were offended - that certainly was not the intent.  I was being genuine in the fact that I don't know nearly what you guys do, as you do this day in and day out, and I don't.  Beleive me, there was no sarcasm intended.  Looks like it was a failed and feeble attempt at being humble.

Again I am grateful for your replies and will move on.

[MarcelBeug]

If I look again, it seems that the condition is in the same column as the column you want to split.

In that case, it can be done, but I would strongly recommend my previous solution.

Actually this code was created as 2 steps and then I applied substittion to make it 1 step.

Again, this is really a ridiculous solution, but you are asking for it.

Anybody else: don't try this at home!!

let
    Source = #table(type table[Classification Group C = text],List.Zip({{"01Hide","02MarcelBeug"}})),
    SplitColumn = Table.SplitColumn(Table.TransformColumns(Source,{{"Classification Group C", each Text.Middle(_, 0,2) & (if Text.EndsWith(_,"Hide") then Text.Middle(_,0) else Text.Middle(_,2))}}), "Classification Group C", Splitter.SplitTextByPositions({0, 2}, false), {"Classification Group C.1", "Classification Group C.2"})
in
    SplitColumn

[MarcelBeug]

There is no third column in my original solution:

let
    Source = #table(type table[Classification Group C = text],List.Zip({{"01Hide","02MarcelBeug"}})),
    AddColumn = Table.AddColumn(Source,"Classification Group C.2", each if Text.EndsWith([#"Classification Group C"],"Hide") then Text.Middle([#"Classification Group C"],0) else Text.Middle([#"Classification Group C"],2), type text),
    #"Extracted First Characters" = Table.TransformColumns(AddColumn, {{"Classification Group C", each Text.Start(_, 2), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted First Characters",{{"Classification Group C", "Classification Group C.1"}})
in
    #"Renamed Columns"

[CLEARIFY]

Well I did something to anger you, and you are just being mean now.   If it is a ridiculous solution then there is no need to continue. 

Your first reply said "you will end up with an additional column", and then your recent reply say it doesn't, so I am confused.

I will take you what you have written and see what I can learn.  Thank you for your time.

[MarcelBeug]

"Additional" as in 1 becomes 2, not 3.