Expand column which has list and date in power bi-CodePudding

I have a column which has a list of dates and dates.

I need to expand the column so that the list of dates under list gets expanded along with the dates in the column.

I have used the below code and getting the "Expression.Error: We cannot convert a value of type List to type Table. Details: Value=[List] Type=[Type]"

Table.TransformColumns([Custom],{{[Custom],each if Value.Is(_,type list) then _ else {_}}})

Code to generate the list

= Table.AddColumn(#"Removed Blank Rows", "New end date", each let StDt = [#"Grant date #(lf)(dd/mm/yyyy)"],

Code to expand the list as per your code.

Table.AddColumn(#"Added Custom", "Custom.1", each Table.TransformColumns([New end date]{{[New end date],each if Value.Is(_,type list) then _ else {_}}}))

CodePudding user response：

This converts non-list rows to lists so that the arrow option shows up

x = Table.TransformColumns(#"PriorStepName", {{"Custom", each if Value.Is(_, type list) then _ else {_} }} )

then use the arrows atop the column to expand. Then change type

Before picture:

after picture

full sample code

let Source =  #table({"a"}, {{"10/1/2020"},{"4/1/2020"},{"6/1/2020"},{"1/1/2020"},{"10/4/2020"},{"10/8/2020"}}),
z = {"5/31/2021","5/15/2020","3/14/2019"},
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]<2 then z else [a], type any),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
// prior is just to set up sample data
x = Table.TransformColumns(#"Removed Other Columns", {{"Custom", each if Value.Is(_, type list) then _ else {_} }} ),
#"Expanded Custom" = Table.ExpandListColumn(x, "Custom"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in #"Changed Type"