Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Allan-Mautante
Frequent Visitor

Excel Formula to Power Bi

Hi Folks,

 

Need your Help on  convert this Excel formula to Dax Power BI fformula.

I allready tried searching but doesn't work.

 

Validitor = Unique Value

Date/Hour= Date of Call

 

 

Allan-Mautante_0-1614417672933.png

 

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

Hello @Allan-Mautante 

 

i can show you how to write these formulas in Power Query, but sometimes this is not the best approach, because Power Query is not Excel after all. 

ISBLANK = 
   [ColumnReference]=null
    or
    [Columnreference]="" depending if your column is empty cell or null

IFERROR = 
    surround your function with try ... yourfunction .... otherwise HereComesFunctionWhenFirstPartGivesError

IF(...,...,...)=
   if check then yourCodeIfCheck=True else yourCodeIfCheck=False

INDEX(MATCH)=
   these kind of function is not existing in Power Query but there is a better alternative and is Table.NestedJoin or Table.Join

I also dont know if your relative cell reference in INDEX or MATCH is intended and needed (basically your cell S4 its changed to S5 in the next row--> is that needed?).  And I really think that is needed --> then you can't use Table.NestedJoin but you would need to go for Table.SelectRows instead and you need to add a Index-column as well. 

Here the formula

Table.AddColumn(AddedIndex, "YourFormula", each try if [C]="" then "" else Table.SelectRows(AddedIndex, (sel)=> sel[Index]>[Index] and sel[S]=[S])[A]{0} otherwise "")

Here the complete code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsovd1TSUQIiE6VYHTDfCcipAGJTmIAzVMAMJuCCUBELAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, C = _t, S = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"C", type text}, {"S", Int64.Type}}),
    AddedIndex = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(AddedIndex, "YourFormula", each try if [C]="" then "" else Table.SelectRows(AddedIndex, (sel)=> sel[Index]>[Index] and sel[S]=[S])[A]{0} otherwise "")
in
    #"Added Custom"

transforms this

Jimmy801_0-1614440828607.png

into this

Jimmy801_1-1614440844410.png

 

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

View solution in original post

1 REPLY 1
Jimmy801
Community Champion
Community Champion

Hello @Allan-Mautante 

 

i can show you how to write these formulas in Power Query, but sometimes this is not the best approach, because Power Query is not Excel after all. 

ISBLANK = 
   [ColumnReference]=null
    or
    [Columnreference]="" depending if your column is empty cell or null

IFERROR = 
    surround your function with try ... yourfunction .... otherwise HereComesFunctionWhenFirstPartGivesError

IF(...,...,...)=
   if check then yourCodeIfCheck=True else yourCodeIfCheck=False

INDEX(MATCH)=
   these kind of function is not existing in Power Query but there is a better alternative and is Table.NestedJoin or Table.Join

I also dont know if your relative cell reference in INDEX or MATCH is intended and needed (basically your cell S4 its changed to S5 in the next row--> is that needed?).  And I really think that is needed --> then you can't use Table.NestedJoin but you would need to go for Table.SelectRows instead and you need to add a Index-column as well. 

Here the formula

Table.AddColumn(AddedIndex, "YourFormula", each try if [C]="" then "" else Table.SelectRows(AddedIndex, (sel)=> sel[Index]>[Index] and sel[S]=[S])[A]{0} otherwise "")

Here the complete code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsovd1TSUQIiE6VYHTDfCcipAGJTmIAzVMAMJuCCUBELAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, C = _t, S = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"C", type text}, {"S", Int64.Type}}),
    AddedIndex = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(AddedIndex, "YourFormula", each try if [C]="" then "" else Table.SelectRows(AddedIndex, (sel)=> sel[Index]>[Index] and sel[S]=[S])[A]{0} otherwise "")
in
    #"Added Custom"

transforms this

Jimmy801_0-1614440828607.png

into this

Jimmy801_1-1614440844410.png

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors