ExpandListColumn changes data type

[Anonymous]

Hi!

I have a problem. I am expanding a list of dates. Unfortunately it changes the value after expanding from dates to int. I don't want that, since I'll have to work with the expanded dates afterwards.

Any idea?

let
    Source = MySQL.Database("xxxxxx", "otrs", [ReturnSingleDatabase=true]),
    otrs_absence_list = Source{[Schema="otrs",Item="absence_list"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(otrs_absence_list,{"create_by", "change_by", "create_time", "reason_rej", "change_time"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([state] = 2)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Duration", each [end] - [start]),
    #"Extracted Days" = Table.TransformColumns(#"Added Custom",{{"Duration", Duration.Days, Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Days", "List", each List.Dates([start], [Duration] + 1, #duration(1, 0, 0, 0))),
    #"Expanded {0}" = Table.ExpandListColumn(#"Added Custom1", "List"),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded {0}",{"id", "type_id", "user_id", "start", "end", "state", "note", "List", "Duration"})
in
    #"Reordered Columns"

[AllisonKennedy]

I don't see what the issue is. Can you try with sample data and share the report or code for that? You should be able to change the data type to Date after you expand the list column.

[Anonymous]

Hi @AllisonKennedy @amitchandak ,

thank you for the reply!

As it is a very big dataset, with interconnected tables it's difficult to upload a sample.

To clarify this further:

What is supposed to happen:

The table includes a 'start' and an 'end' column.

We now expand this daterange (code above) and then want to filter these dates.

This works on desktop.

What happens:

As soon as we upload the dataset to online and refresh it with the Gateway - the type is being set to Integer and the values are 0. Therefore all other values are missing.

Since this works on desktop without a problem and doesn't online I am confused.

The gateway has full access, is updated and works without a problem on other reports. The db-access also works.

Do you have an idea?

Online report view (data missing)Desktop visual view (correct data)

EDIT:

What we have tried:

- building a measure which creates a virtual table instead of expanding and then gets filtered

- duplicating the (expanded) - "List" column and then changing the datatype

- simply changing the datatype of the expanded column afterwards

None of these have been successful 😞

[Anonymous]

EDIT:

This issue only appear when a Dynamic-Date Table is linked.

I'll add a screenshot of the model to this posts.

Help would be gladly appreciated.

[amitchandak]

@Anonymous , refer if this can help

https://blog.crossjoin.co.uk/2017/09/25/setting-data-types-on-nested-tables-in-m/