cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pureswing Frequent Visitor
Frequent Visitor

a more efficient way to count between X numbers (columns)?

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: a more efficient way to count between X numbers (columns)?

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"

 

Specializing in Power Query Formula Language (M)

View solution in original post

MarcelBeug Super Contributor
Super Contributor

Re: a more efficient way to count between X numbers (columns)?

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

 

Specializing in Power Query Formula Language (M)

View solution in original post

5 REPLIES 5
Highlighted
Super User
Super User

Re: a more efficient way to count between X numbers (columns)?

The general approach I would suggest is to

  • Add an Index column
  • split into columns as before
  • unpivot the new columns

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
MarcelBeug Super Contributor
Super Contributor

Re: a more efficient way to count between X numbers (columns)?

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"

 

Specializing in Power Query Formula Language (M)

View solution in original post

pureswing Frequent Visitor
Frequent Visitor

Re: a more efficient way to count between X numbers (columns)?

@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 Smiley Frustrated

MarcelBeug Super Contributor
Super Contributor

Re: a more efficient way to count between X numbers (columns)?

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

 

Specializing in Power Query Formula Language (M)

View solution in original post

pureswing Frequent Visitor
Frequent Visitor

Re: a more efficient way to count between X numbers (columns)?

magnificent! Thanks Marcel Smiley Happy

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 119 members 2,606 guests
Please welcome our newest community members: