transformation of gantt char data to the programmer

[froxas]

Hello

I have car resertvation data as in the first table, it is usual as the gantt char option.

but I want to convert to data as in the second table. means that I want to add for each day period value 1 to each booking.

I have to do to make a visual programmer (timeline) with matrix table, where only the background color of the value 1 and get visual.

Does anyone have any idea how to make such a transformation?

[v-lid-msft]

Hi @froxas ,

We can try to add five steps in the end of your query to meet your requirement:

such as the origin query like following:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8lDSUTIyMDLQNTAFIjjHTNfYAMgJMFSK1UFWBBWHcMx1DU0RigLg4ia6BobIxoI4AUZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Car number" = _t, From = _t, Till = _t, Project = _t]),
    NameOfYourLastStep = Table.TransformColumnTypes(Source,{{"Car number", type text}, {"From", type date}, {"Till", type date}, {"Project", type text}})
in
    NameOfYourLastStep

Then we change it to following:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8lDSUTIyMDLQNTAFIjjHTNfYAMgJMFSK1UFWBBWHcMx1DU0RigLg4ia6BobIxoI4AUZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Car number" = _t, From = _t, Till = _t, Project = _t]),
    NameOfYourLastStep = Table.TransformColumnTypes(Source,{{"Car number", type text}, {"From", type date}, {"Till", type date}, {"Project", type text}}),
    MinDate = Table.Min(NameOfYourLastStep,{"From"})[#"From"],
    MaxDate = Table.Max(NameOfYourLastStep,{"Till"})[#"Till"],
    CarNumberTable = Table.Distinct(Table.SelectColumns(NameOfYourLastStep,{"Car number","Project"})),
    DateTable = Table.ExpandListColumn(Table.AddColumn(CarNumberTable,"Date",each List.Dates(MinDate,Duration.Days(MaxDate-MinDate)+1,#duration(1,0,0,0))), "Date"),
    FinalTable = Table.AddColumn(DateTable,"Value", 
                                each let car = [Car number], pro = [Project], d = [Date] in
                                if Table.RowCount(Table.SelectRows(NameOfYourLastStep,each [Car number]=car and [Project]=pro and [From] <= d and [Till] >= d))>0 then 1 else 0)
in
    FinalTable

MinDate/MaxDate is the date range, then we get distinct rows for carnumber and project, add every date between MinDate and MaxDate to them, In FinalTable, we add a value to determine if this date exist in the origin table.

If it doesn't meet your requirement, Could you please show the exact expected result based on the tables that you have shared?

By the way, PBIX file as attached.

Best regards,

[froxas]

@v-lid-msft  thanks

that's interesting solution, i will check it.

is there also method to make it with DAX creating table? maybe it's more easer, readable and maintainable solution?

[v-lid-msft · Accepted solution]

Hi @froxas ,

Sure, we can create a calculated table to meet your requirement:

NewTable =
ADDCOLUMNS (
    CROSSJOIN (
        DISTINCT (
            SELECTCOLUMNS ( 'Table', "CarNumber", [Car number], "Project", [Project] )
        ),
        CALENDAR ( MIN ( 'Table'[From] ), MAX ( 'Table'[Till] ) )
    ),
    "Value",
    VAR d = [Date]
    VAR cn = [CarNumber]
    VAR p = [Project]
    RETURN
        IF (
            CALCULATE (
                COUNTROWS ( 'Table' ),
                'Table'[From] <= d,
                'Table'[Till] >= d,
                'PQTable'[Car number] = cn,
                'PQTable'[Project] = p
            ) > 0,
            1,
            0
        )
)

By the way, PBIX file as attached.

Best regards,

[parry2k]

@froxas how you are getting 0 or 1 value in value column?

[froxas]

@parry2k  that's how i need to transform my table. I need to create new table where i have all days and by every day value 1 if this reservatio  is in that period.