How to input dynamic custom column function

[Anonymous]

Hi guys,

I have 2 queries, Formula and Transform. In the Transform query, I want to input a Custom Column function that changes according to the Levels (LV) in the Formula query (pic 1 - Formula query).

My idea is to pre-define the segments of the code (column Formula), and then combine the segments into a complete code as a variable named CustomFormula (pic 2) and feed it into the Custom Column formula 

However, it does not work since the Custom Column recognizes the variable as text. Result is the formula text repeated for all rows.

Any ideas to convert the text into actual formula? Or any other alternatives would be appreciated. Thank you!

[PhilipTreacy]

Hi @Anonymous 

Download example PBIX file with this code

You can create a Custom Column in Power Query with this code

= Table.AddColumn(Source, "Level", each if List.ContainsAny({"0".."9"}, Text.ToList(Text.Replace([Column1],".",""))) then 3 else if List.ContainsAny({"A".."H"}, Text.ToList(Text.Replace([Column1],".",""))) then 1 else 2)

Which gives you this

NOTE Because you have levels that are identified by letters and you have a different level indicated by Roman Numerals, you are going to get an overlap because Roman Numerals are letters.  So in the list I'm using to detect levels indicated by letters, I'm only using a list from A to H, because I is Roman numeral = 1.

If you have more than 8 levels indicated by letters then some modification to this code will be required.

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

Hi @StefanoGrimaldi , @PhilipTreacy , here is what I want:

Input: I have a stepped Excel report like the picture below.

Output: I want to insert this into PBI and have a column Level that identifies which row in column A is level 1, level 2 ...

In this case, I have 3 levels. Level 1 starts with "A", "B", "C", ... Level 2 starts with "I", "II", "III", ... and Level 3 with numerals. Thus I insert a Custom Column with a formula:

if List.Contains({"A", "B", "C"}, Text.Start([Column1], 1)) then 1 else

if List.Contains({"I", "II", "III"}, Text.Start([Column1], 1)) then 2 else 3

And I got what I want:

Now, I want to reuse this model for different stepped reports. My pain point is: The number of level is different between each files, so the above formula is wrong if I have 4, 5 levels. The identification rule may be different too. So as I understand, I need a dynamic formula that changes according to the number of level / rule.

I try to accomplish this by Enter Data to use as formula input, but it does not work. Hence enter my question above 😄

[Jimmy801 · Accepted solution]

Hello @Anonymous 

you can try this code. With this you are able to define you level indicators on your own. In the variables Level1, Level2 and Level 3 you can input your indicators. For sure if you get at the letter I. then you have a problem, then you would need to adapt the solution a little bit. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Ncy7DcAwCATQVSLXUaRkg3yazGC5QEIugI7dWMCLxQan5N0dOadzW5oxiBLWZqmsOV2DQBnpl9tFJrEAKVZPXk94rOPuAM1URtVlj2+CxAFHb2gzFK7k8MxGTMoH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YourColumn = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"YourColumn", type text}}),
    Level1 = {"A.", "B.", "C.", "D."},
    Level2 = {"I.", "II.", "III."},
    Level3 = {"1.", "2.", "3."},
    AddLevel = Table.AddColumn
    (
        #"Changed Type",
        "Level",
        each 
        let 
            GetLevel = Text.Split([YourColumn], " "){0},
            WriteLevel = if List.Contains(Level1, GetLevel) then 1 else if List.Contains(Level2, GetLevel) then 2 else if List.Contains(Level3, GetLevel) then 3 else null
        in WriteLevel
    )
in
    AddLevel

Copy paste this code to the advanced editor in a new blank query to see how the solution works. 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[PhilipTreacy]

Hi @Anonymous 

it's very confusing.  Maybe if you just show us your source data and queries and explain/illustrate what the final result you want is, we can work out the in between bits to get from the start to the end.

regards

Phil

[StefanoGrimaldi]

hey, 

like you are working it on the power query I would add a new column for each segment, them  a last column with the if scenarios to evaluate and use the previous columns results, them you delete those column and stay with the last one.