I want to change the formula from excel into power bi Column

[UjjawalTyagi518]

i have calculated 2 column in my excel sheet naming (diff1 and diff 2), i want to do the same in power bi.
i have shared the data and the formula i m using in excel to calculte these colomn.

[ronrsnfld · Accepted solution]

Here is some M Code that will generate your DIFF columns from your Excel table.

I used the List.Generate function to generate those columns, although other methods are possible

  I have not tested extensively so if there are data sets for which this doesn't work, please share them.

Source

note that I removed the blank row.  If this is not practical in your data source, you can always do it as an early step in the M-Code

M Code

let

//change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Date", type date}, {"Sec Invoice no", type text}, {"Amt 1", Int64.Type}, {"**bleep** Amt", Int64.Type}, 
        {"Pri Invoice No", type text}, {"QTY 1", Int64.Type}, {"QTY 2", Int64.Type}}),

//Generate Diff Columns
    DIFF1 = List.Generate(
        ()=>[d=#"Changed Type"[QTY 1]{0}-#"Changed Type"[Amt 1]{0}, idx=0],
        each [idx] < Table.RowCount(#"Changed Type"),
        each [d=if #"Changed Type"[Pri Invoice No]{[idx]+1}<>#"Changed Type"[Pri Invoice No]{[idx]}
                    and #"Changed Type"[Sec Invoice no]{[idx]+1} <> #"Changed Type"[Sec Invoice no]{[idx]}
                then #"Changed Type"[QTY 1]{[idx]+1} - #"Changed Type"[Amt 1]
                else if #"Changed Type"[Pri Invoice No]{[idx]+1} = #"Changed Type"[Pri Invoice No]{[idx]}
                    and #"Changed Type"[Sec Invoice no]{[idx]+1} <> #"Changed Type"[Sec Invoice no]{[idx]}
                then [d] - #"Changed Type"[Amt 1]{[idx]+1}
                else #"Changed Type"[QTY 1]{[idx]+1}+[d], idx=[idx]+1],
        each [d]),
    DIFF2 = List.Generate(
        ()=>[d=#"Changed Type"[Amt 1]{0}, idx=0],
        each [idx] < Table.RowCount(#"Changed Type"),
        each [d=if DIFF1{[idx]+1}<0 then DIFF1{[idx]+1} + #"Changed Type"[Amt 1]{[idx]+1}
                    else if #"Changed Type"[Sec Invoice no]{[idx]+1}<> #"Changed Type"[Sec Invoice no]{[idx]}
                    then #"Changed Type"[Amt 1]{[idx]+1}
                    else #"Changed Type"[QTY 1]{[idx]+1} - DIFF1{[idx]+1}, idx = [idx]+1],
        each [d]),

//add the diff columns to the table
    #"Added Diff Columns" = Table.FromColumns(
        Table.ToColumns(#"Changed Type") & {DIFF1} & {DIFF2},
        Table.ColumnNames(#"Changed Type") & {"DIFF1","diff 2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Diff Columns",{{"DIFF1", Int64.Type}, {"diff 2", Int64.Type}})
      
in
    #"Changed Type1"

Results

[UjjawalTyagi518]

@ronrsnfld 
thank you so much for this solution

[NikhilChenna]

Hi @UjjawalTyagi518 ,

I'm trying to replicate the same highlighted above. 

But a question for Diff1, in the second condition where you are checking if E3=E2 and C3<>C2, then how  can you take the output column H2 for result of your if condition. it is possible to select in excel any cell.

Can you tell us why are you taking it ? I'm half way through i got the first part of it. 

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[UjjawalTyagi518]

Hi @NikhilChenna |
actuccly i m comparing the privous row with the current row in this logic i.e, if E3=E2 & C3 is not equal to C2 (if this condition gets true then minus the prvious *diff* with current Amt 1.
hope you understand, let me know if it still confuess a bit

[UjjawalTyagi518]

Hi @NikhilChenna 
did you understand the logic now ????/?

[v-easonf-msft]

Hi, @UjjawalTyagi518 

Converting the formula from excel to PowerBI column can be quite difficult.

Dax is not suitable for this kind of recursive operation.

Best Regards,
Community Support Team _ Eason

[UjjawalTyagi518]

Hi @v-easonf-msft 
till now after trying to solve thi smany times, i understand this,
can you help me to do it in power query?
thanks

[NikhilChenna]

Hi @UjjawalTyagi518 , I think it will not be possible in power query too. M I righ @v-easonf-msft .

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[UjjawalTyagi518]

hi @NikhilChenna 
it is not possible in dax bcoz it is a recursion calculation and in dax we cant do that as of now,
but it would be great help if we can do it in power query.

thanks & regards 
ujjawal

[NikhilChenna]

Hi @UjjawalTyagi518 ,

I'll check and let you know. 

[UjjawalTyagi518]

@NikhilChenna 
thanks alot for your help

[NikhilChenna]

Hi @UjjawalTyagi518 ,

Calculating Diff 2 will be easy if you tell me about the question i asked about Diff 1 .

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!