Forum Discussion

gersinzon's avatar
gersinzon
New Member
2 years ago
Solved

Convert DAX to M

Hi!
I need some help to convert this code into M.
DAX presents out of memory big base.
Thanks

1. To create a calculated table(DateTable):
DateTable =
SELECTCOLUMNS(
GENERATE(VALUES('Table'[orderNo]),GENERATESERIES(MIN('Table'[status date]),MAX('Table'[status date]))),
"orderNo",[orderNo],"startDate",[Value]
)

2. To create a calculated column:
_Status = VAR _a = CALCULATE ( MAX ( 'Table'[status] ), FILTER ('Table',[status date] <= EARLIER ( 'DateTable'[startDate] )&& [orderNo] = EARLIER ( 'DateTable'[orderNo] )) ) RETURN // _a IF ( CALCULATE (DISTINCTCOUNT ( 'Table'[status] ),FILTER ( 'Table', [orderNo] = EARLIER ( 'DateTable'[orderNo] ) )) <= 1, _a, IF ( [startDate] <= CALCULATE (MAX ( 'Table'[status date] ),FILTER ( 'Table', [orderNo] = EARLIER ( 'DateTable'[orderNo] ) )) && [startDate] >= CALCULATE (MIN ( 'Table'[status date] ),FILTER ( 'Table', [orderNo] = EARLIER ( 'DateTable'[orderNo] ) )), _a ) )

 

 

Need Help
  • aduguid's avatar
    aduguid
    2 years ago

    Create a Calculated Table in M

    let
    Source = YourSourceTable, // Replace with the actual source table
    OrderDates = Table.SelectColumns(Source, {"orderNo", "status date"}),
    Grouped = Table.Group(OrderDates, {"orderNo"}, {{"MinDate", each List.Min([status date]), type date}, {"MaxDate", each List.Max([status date]), type date}}),
    Expanded = Table.ExpandListColumn(Table.TransformColumns(Grouped, {"MaxDate", each List.Generate(() => _, each _ <= Grouped[MinDate]{0}, each Date.AddDays(_, 1))}), "MaxDate"),
    Renamed = Table.RenameColumns(Expanded, {{"MinDate", "orderNo"}, {"MaxDate", "startDate"}})
in
    Renamed

    Then merge the "DataTable" with the original table

    let
    Source = YourSourceTable, // Replace with the actual source table
    DateTable = PreviousStep, // Replace with the step where you created DateTable
    MergedTables = Table.NestedJoin(DateTable, {"orderNo"}, Source, {"orderNo"}, "MergedTable", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(MergedTables, "MergedTable", {"status date", "status"}, {"status date", "status"}),

    AddedCustom = Table.AddColumn(Expanded, "_Status", each 
        let
            currentOrderNo = [orderNo],
            currentStartDate = [startDate],
            filteredTable = Table.SelectRows(Source, each ([orderNo] = currentOrderNo) and ([status date] <= currentStartDate)),
            maxStatus = List.Max(filteredTable[status]),
            distinctStatusCount = List.NonNullCount(List.Distinct(filteredTable[status])),
            minStatusDate = List.Min(filteredTable[status date]),
            maxStatusDate = List.Max(filteredTable[status date])
        in
            if distinctStatusCount <= 1 then maxStatus
            else if currentStartDate <= maxStatusDate and currentStartDate >= minStatusDate then maxStatus
            else null
    ),
    RemovedColumns = Table.RemoveColumns(AddedCustom,{"status date", "status"})
in
    RemovedColumns

1 Reply

  • aduguid's avatar
    aduguid
    Memorable Member
    2 years ago

    Create a Calculated Table in M

    let
    Source = YourSourceTable, // Replace with the actual source table
    OrderDates = Table.SelectColumns(Source, {"orderNo", "status date"}),
    Grouped = Table.Group(OrderDates, {"orderNo"}, {{"MinDate", each List.Min([status date]), type date}, {"MaxDate", each List.Max([status date]), type date}}),
    Expanded = Table.ExpandListColumn(Table.TransformColumns(Grouped, {"MaxDate", each List.Generate(() => _, each _ <= Grouped[MinDate]{0}, each Date.AddDays(_, 1))}), "MaxDate"),
    Renamed = Table.RenameColumns(Expanded, {{"MinDate", "orderNo"}, {"MaxDate", "startDate"}})
in
    Renamed

    Then merge the "DataTable" with the original table

    let
    Source = YourSourceTable, // Replace with the actual source table
    DateTable = PreviousStep, // Replace with the step where you created DateTable
    MergedTables = Table.NestedJoin(DateTable, {"orderNo"}, Source, {"orderNo"}, "MergedTable", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(MergedTables, "MergedTable", {"status date", "status"}, {"status date", "status"}),

    AddedCustom = Table.AddColumn(Expanded, "_Status", each 
        let
            currentOrderNo = [orderNo],
            currentStartDate = [startDate],
            filteredTable = Table.SelectRows(Source, each ([orderNo] = currentOrderNo) and ([status date] <= currentStartDate)),
            maxStatus = List.Max(filteredTable[status]),
            distinctStatusCount = List.NonNullCount(List.Distinct(filteredTable[status])),
            minStatusDate = List.Min(filteredTable[status date]),
            maxStatusDate = List.Max(filteredTable[status date])
        in
            if distinctStatusCount <= 1 then maxStatus
            else if currentStartDate <= maxStatusDate and currentStartDate >= minStatusDate then maxStatus
            else null
    ),
    RemovedColumns = Table.RemoveColumns(AddedCustom,{"status date", "status"})
in
    RemovedColumns