Forum Discussion

CroWorC's avatar
CroWorC
Frequent Visitor
5 years ago

Dataflow Convert from line to column with conditions

Hello,  As a relative new user of PowerBI dataflow i'm stuck with this problem: As source there is a table with id's, orderID's, Events, date of event and if needed a plandate. Now I want to conve...
Need Help
Show and Tell
CroWorC's avatar
CroWorC
Frequent Visitor
5 years ago

Hi Smauro,

Sorry for the late reaction and thanks for the possible solution. The data in my table seems to be too big for this rebuild. I have + 3 milion records in the table and the solution You gave runs out of time. So I tried to select a part of the records but it seems that Powerflow is using the original table still. It keeps on running out of time. Then I tried to get fewer records from the database using SQL statement in stead of chosing a table, but that won't work in Powerflow (same SQL works in PowerBI Dataset) So for now I'm running out of options. I wil come back if my dataproblem is solved. 

  • Smauro's avatar
    Smauro
    Solution Sage
    5 years ago

    Hi there, yes, this solution would be very heavy in your dataset.

     

    Best I could hastly do is this:

    let
        Source = Table.TransformColumnTypes(YourTable, {{"ID", Int64.Type}, {"date", Date.Type}, {"plandate", Date.Type}}),
        
        EventSort = [
            Start = 5,
            Plan = 3,
            Plan1 = 4, 
            OnHold = 1,
            OffHold = 2,
            Finish = 6
        ],
        rec = [
            Plan1ID = null,
            OnHoldID = null,
            PlanOldID = null,
            PlanNewID =null,
            PdtPlan1 = null,
            PdPlanOld = null,
            PdPlanNew = null,
            dtOnHold = null,
            dtOffHold = null
        ],
        recType = type {[
            Plan1ID = Int64.Type,
            OnHoldID = Int64.Type,
            PlanOldID = Int64.Type,
            PlanNewID =Int64.Type,
            PdtPlan1 = Date.Type,
            PdPlanOld = Date.Type,
            PdPlanNew = Date.Type,
            dtOnHold = Date.Type,
            dtOffHold = Date.Type
        ]},


        #"Fix Table Order" = Table.AddIndexColumn( Table.Sort(
            Table.AddColumn(Source, "EventSort", each Record.Field(EventSort, [Event]), Int64.Type),
            {"Order", "date", "EventSort"}), "temp"),
        #"Merge Data" = Table.CombineColumns(#"Fix Table Order",
            {"ID", "date", "plandate", "Event"},
            (x) => {x{3}} & {[ID = x{0}, date = x{1}, plandate=x{2} ]},"Events"),
        GroupPerOrder = Table.Group(#"Merge Data", {"Order"}, {{"Holds", each
            List.Select(List.Accumulate([Events], {rec}, (s,c) =>
            if c{0} = "Plan1" then 
                let new = [PdtPlan1 = c{1}[plandate], Plan1ID = c{1}[ID]]
                in if List.Last(s)[OnHoldID] = null then List.RemoveLastN(s, 1) & {List.Last(s) & new}
                else s & {rec & new}
            else if c{0} = "Plan" then
                let new = [PlanOldID = c{1}[ID], PdPlanOld = c{1}[plandate]]
                in if List.Last(s)[OnHoldID] = null then List.RemoveLastN(s, 1) & {List.Last(s) & new}
                    else if List.Last(s)[PlanNewID] = null then
                        List.RemoveLastN(s, 1) & {List.Last(s) &
                        Record.RenameFields(new, {{"PlanOldID", "PlanNewID"}, {"PdPlanOld", "PdPlanNew"}})}
                        & {rec & new}
                    else s & {rec & new}
            else if c{0} = "OnHold" then
                let new = [dtOnHold = c{1}[date], OnHoldID = c{1}[ID]]
                in if List.Last(s)[OnHoldID] = null then List.RemoveLastN(s, 1) & {List.Last(s) & new}
                else s & {rec & new}
            else if c{0} = "OffHold" then
                let new = [dtOffHold = c{1}[date]]
                in List.RemoveLastN(s, 1) & {List.Last(s) & new}
            else s
        ), each _[OnHoldID] <> null ), recType}}, GroupKind.Local),
        #"Expanded Holds' list" = Table.SelectRows(Table.ExpandListColumn(GroupPerOrder, "Holds"), each [Holds] <> null),
        #"Expanded Holds' records" = Table.ExpandRecordColumn(#"Expanded Holds' list", "Holds", Record.FieldNames(rec))
        
    in
        #"Expanded Holds' records"

     

     

    Instead of buffering and filtering everytime, it sorts the table once and then groups locally. Give it a try and let me know 🙂

    (YourTable is your table's query name)

     

    Cheers,

    Spyros