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.
I have a column (around 5k rows) with text type values similar to this 145-157-169-181-200, this numbers represents frames and I need the diference between frames, something like 12-12-12-19.
I have achieved the desired result doing split column by delimiter "-" then a custom column for each frame calculation (col2-col1, col3-col2, col4-col3, etc.) and a merge between all custom columns, final result (12-12-12-19). The problem is that I have values with more than 30 different frames and things get complicated with 30 columns or more. So is there a more efficient way to achieve the result?
Regards, Liu
Solved! Go to Solution.
My suggestion would be to create a custom function Differences:
(String as nullable text) as nullable text => let List = Text.Split(String,"-"), Numbers = List.Transform(List, Number.From), NumbersWithPrevious = List.Zip({List.Skip(Numbers),List.FirstN(Numbers,List.Count(Numbers)-1)}), Subtracted = List.Transform(NumbersWithPrevious, each _{0}-_{1}), Texts = List.Transform(Subtracted, Text.From), Combined = Text.Combine(Texts,"-") in Combined
Now you can add a column to your table with option "Invoke Custom Function".
Resulting query (I manually added , type text in the second line):
let Source = #table(type table[Frames = text],{{"145-157-169-181-200"},{"145-200"}}), #"Invoked Custom Function" = Table.AddColumn(Source, "Differences", each Differences([Frames]), type text) in #"Invoked Custom Function"
Probably you have an older version of Power Query without List.Zip.
An alternative is to use Table.FromColumns and - in the next step - subtract the columns using Table.TransformRows.
Table.TransformRows is probably not well known with a broader audience.
Basicallly it does the same as adding a column to a table and next drill down into that column (resulting in a list with only the values from the added column, so all other columns deleted).
Revised code for function Differences:
(String as nullable text) as nullable text => let List = Text.Split(String,"-"), Numbers = List.Transform(List, Number.From), NumbersWithPrevious = Table.FromColumns({List.Skip(Numbers),List.FirstN(Numbers,List.Count(Numbers)-1)}), Subtracted = Table.TransformRows(NumbersWithPrevious, each [Column1]-[Column2]), Texts = List.Transform(Subtracted, Text.From), Combined = Text.Combine(Texts,"-") in Combined
The general approach I would suggest is to
this should give you a table with one row per frame. Then with each index number, you should have rows that represent the frames. Then use the technique is show in this video to find the previous record and create the difference column. https://m.youtube.com/watch?v=xN2IRXQ2CvI
My suggestion would be to create a custom function Differences:
(String as nullable text) as nullable text => let List = Text.Split(String,"-"), Numbers = List.Transform(List, Number.From), NumbersWithPrevious = List.Zip({List.Skip(Numbers),List.FirstN(Numbers,List.Count(Numbers)-1)}), Subtracted = List.Transform(NumbersWithPrevious, each _{0}-_{1}), Texts = List.Transform(Subtracted, Text.From), Combined = Text.Combine(Texts,"-") in Combined
Now you can add a column to your table with option "Invoke Custom Function".
Resulting query (I manually added , type text in the second line):
let Source = #table(type table[Frames = text],{{"145-157-169-181-200"},{"145-200"}}), #"Invoked Custom Function" = Table.AddColumn(Source, "Differences", each Differences([Frames]), type text) in #"Invoked Custom Function"
@MarcelBeug I tried your suggestion and got this error : Expression error: The name 'List.Zip' wasn't recognized. Make sure it's spelled correctly. I know it's well spelled, so i'm kinda lost here
Probably you have an older version of Power Query without List.Zip.
An alternative is to use Table.FromColumns and - in the next step - subtract the columns using Table.TransformRows.
Table.TransformRows is probably not well known with a broader audience.
Basicallly it does the same as adding a column to a table and next drill down into that column (resulting in a list with only the values from the added column, so all other columns deleted).
Revised code for function Differences:
(String as nullable text) as nullable text => let List = Text.Split(String,"-"), Numbers = List.Transform(List, Number.From), NumbersWithPrevious = Table.FromColumns({List.Skip(Numbers),List.FirstN(Numbers,List.Count(Numbers)-1)}), Subtracted = Table.TransformRows(NumbersWithPrevious, each [Column1]-[Column2]), Texts = List.Transform(Subtracted, Text.From), Combined = Text.Combine(Texts,"-") in Combined
magnificent! Thanks Marcel
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.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |