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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Yingjing
Frequent Visitor

convert dax to power query m

https://community.fabric.microsoft.com/t5/Desktop/Inventory-aging-report/m-p/2493851#M887356

 

hi all,

 

i got a issue similar to the link above but with power query m.

please help to convert the dax to power query m, thanks!

1 ACCEPTED SOLUTION

@Yingjing 

let
    Source = your_table,
    a = List.Buffer(Table.ToRecords(Table.Sort(Source, "Index"))),
    count = List.Count(a),
    provision = List.Sum(Source[Provision]),
    gena = 
        List.Generate(
            () =>
                [i = 0,
                prov_before = provision,
                prov_after = List.Min({0, a{i}[Value] + prov_before}),
                row = a{i} & [Result = prov_before - prov_after]],
            (x) => x[i] < count,
            (x) => 
                [i = x[i] + 1,
                prov_before = x[prov_after],
                prov_after = List.Min({0, prov_before + a{i}[Value]}),
                row = a{i} & [Result = prov_before - prov_after]],
            (x) => x[row]
        ),
    tbl = Table.FromRecords(gena)
in
    tbl

View solution in original post

4 REPLIES 4
AlienSx
Super User
Super User

Hello, @Yingjing 

let
    Source = your_table,
    fx_fifo = (tbl as table) =>
        [a = List.Buffer(Table.ToRecords(Table.Sort(tbl, "Date"))),
        count = List.Count(a),
        stock_out = List.Sum(tbl[Stock Out]),
        gena = 
            List.Generate(
                () =>
                    [i = 0,
                    sold = a{i}[Stock Out] = null,
                    stock_before = stock_out,
                    stock_after = if sold then List.Max({0, stock_before - a{i}[New Stock]}) else stock_before,
                    row = a{i} & [Result = if sold then List.Max({0, a{i}[New Stock] - stock_before}) else null]],
                (x) => x[i] < count,
                (x) => 
                    [i = x[i] + 1,
                    sold = a{i}[Stock Out] = null,
                    stock_before = x[stock_after],
                    stock_after = if sold then List.Max({0, stock_before - a{i}[New Stock]}) else stock_before,
                    row = a{i} & [Result = if sold then List.Max({0, a{i}[New Stock] - stock_before}) else null]],
                (x) => x[row]
            ),
        res = Table.FromRecords(gena)][res],
    group = Table.Group(Source, "Branch", {{"trn", fx_fifo}}),
    expand = Table.ExpandTableColumn(group, "trn", {"Date", "New Stock", "Stock Out", "Result"})
in
    expand

Hi Alien,

 

hows the pq m would be if it is for the table below (allocate "provision" according to sequence of "index", but not exceeding "value"). Result is the outcome I want.

index 1 "result"= -1784604 (mqx of "Value")

index 2 "result" = -457394 (-2241997.62+1784604 )

AttributeIndexValueProvisionResult
> 5 years11784603.62-2241997.62-1784604
> 4 - 5 years2670995.440-457394
> 3 - 4 years3577133.7800
> 2 - 3 years4544003.5100
> 1 - 2 years53913885.9400
> 6 mths - 1 year69673161.9100
< 6 mths750572704.5500

 

@Yingjing 

let
    Source = your_table,
    a = List.Buffer(Table.ToRecords(Table.Sort(Source, "Index"))),
    count = List.Count(a),
    provision = List.Sum(Source[Provision]),
    gena = 
        List.Generate(
            () =>
                [i = 0,
                prov_before = provision,
                prov_after = List.Min({0, a{i}[Value] + prov_before}),
                row = a{i} & [Result = prov_before - prov_after]],
            (x) => x[i] < count,
            (x) => 
                [i = x[i] + 1,
                prov_before = x[prov_after],
                prov_after = List.Min({0, prov_before + a{i}[Value]}),
                row = a{i} & [Result = prov_before - prov_after]],
            (x) => x[row]
        ),
    tbl = Table.FromRecords(gena)
in
    tbl

I'm grateful that your solution works, thanks a lot!😁

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors