help with two formulas from EXCEL to use in queries

[pureswing]

Hello guys, here again looking for help. I've been using for months two excel formulas without problems. Now I want to go one step ahead and use it directly in power query but I'm still a novice in M lenguage, the folrmulas in cuestion are :

=IF(A2<>A1,B2,C1 & " / " & B2)

and: 

=IF(A2<>A3,CONCATENATE(C2),"")

And example of the result of using both formula:

Order	Design	=IF(A2<>A1,B2,C1 & " / " & B2)	=IF(A2<>A3,CONCATENATE(C2),"")
5666	MAR-324	MAR-324	MAR-324
6987	EVE-001	EVE-001
6987	EVE-012	EVE-001 / EVE-002	EVE-001 / EVE-002
8012	LAT-101	LAT-101
8012	EVE-001	LAT-101 / EVE-001
8012	LIN-005	LAT-101 / EVE-001 / LIN-005	LAT-101 / EVE-001 / LIN-005
9823	MAR-310	MAR-310	MAR-310

the final step would be a filter row on column D to eliminate blanks rows.... I appreciate any help please 

By the way, sorry if this might be a dumb question but I wonder if there is any web or blog with information on how to "convert" excell formulas to M language using real escenarios ??(for dummies xd). It happens that I don't have time to read for 3 hours everytime I am stuck with a formula so it wold be great to find some information similar to this: 

Funtion	Excel	Power Query	Result
IF	Example	Example	Obtaining the same result

Regards, Liu

[Greg_Deckler]

Looks like you are doing things with previous rows and/or next rows in a table. I suppose that might be possible in M language but I have never personally tried that other than perhaps referencing the table twice with an Index and doing a join perhaps. Perhaps @MarcelBeug or @ImkeF can help with that.

In general, an IF statement in M code looks like this:

= if [Column] < 2 then 1 else 2

I think what you would have to do in M code would be to grab the next 2 rows by doing a filter into a temp table essentially (a query step that sits outside of the main flow). I'll try to come up with an example. Then, in theory you could use it somehow. I'm not really sure how that would all work though, you'd have to write a function and do some looping. Nasty. I think I would just import the table 3 times with an Index column that started at 2 for the first time, 1 for the second one and 0 for the third one. Then you could join all three tables on these Index columns and effectively have all three rows in the same row. Something like that.

[pureswing]

Hi smoupre, well @MarcelBeug help me a couple days ago with this post, and his solution was a bless, I spend a whole week looking for a solution and he did it in just minutes. Sadly there is no option for donations in the forum otherwise I would gladly give a contribution for your time and help guys:) 

In the beginning I was okay using two tables (one using the formulas and another one with the result) but the data its getting bigger and I have some performance issues, so I am trying to get the same result with just one table and I think (maybe I'm wrong) the performance could improve .

[Greg_Deckler]

Take a look at this thread:

https://community.powerbi.com/t5/Desktop/Calculating-Price-change-and-Growth/m-p/329158#M147023

It has some things in there working with EARLIER that might help you as this is a way to work with other rows in DAX (not Power Query). May or may not be a fit. The MAXX, SUMX and MINX functions essentially do table scans so for large data sets they can be slow.

[MarcelBeug · Accepted solution]

Just group by order, defining some dummy operation (e.g. Max) for Design, and then adjust the code to have the ext combined.

As follows:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", Int64.Type}, {"Design", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Order"}, {{"Result", each Text.Combine([Design], " / "), type text}})
in
    #"Grouped Rows"

[pureswing]

Amazing it works!!!,  thanks guys @Greg_Deckler @MarcelBeug