Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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.
Proud to be a Super User!
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 😄
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
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
Proud to be a Super User!
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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.