Formule (TRANSPOSE) werkt niet in tafel

[Hefan]

Hoi,

Weet iemand waarom mijn formule =TRANSPOSE(SEQUENCE(B11-A11+1;;A11)) in de bijgevoegde file niet werkt in een tafel en een foutmelding geeft?

Met vriendelijke groeten,

Hefan

 

[peter59]

Je schrijft het zelf al. Het is een tabel en daar werkt het niet in.

 

[JEC.]

Dynamisch via Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    cTypes = Table.TransformColumnTypes(Source,{{"Startdatum", type date}, {"Einddatum", type date}}),
    dList = Table.AddColumn(cTypes, "dList", each  let dt = Number.From([Startdatum]) in  {dt..dt+Number.From([Einddatum]-[Startdatum])}),
    extract = Table.TransformColumns(dList, {"dList", each Text.Combine(List.Transform(_, Text.From), ":"), type text}),
    xMaxList =  List.Transform({
        1..List.Max(
         Table.AddColumn(extract, "Custom", each
            List.Count(
                Text.PositionOfAny([dList], {":"}, Occurrence.All)
            ))[Custom]
        ) +1
    }, each "C" & Text.From(_)),
    lCount = List.Count(xMaxList),
    split = Table.SplitColumn(extract, "dList", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), xMaxList),
    toInt = Table.TransformColumnTypes(split, List.Zip({xMaxList,List.Repeat({Int64.Type},lCount)})),
    result = Table.TransformColumnTypes(toInt, List.Zip({xMaxList,List.Repeat({type date},lCount)}))
in
    result

 

[Hefan]

Dynamisch via Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    cTypes = Table.TransformColumnTypes(Source,{{"Startdatum", type date}, {"Einddatum", type date}}),
    dList = Table.AddColumn(cTypes, "dList", each  let dt = Number.From([Startdatum]) in  {dt..dt+Number.From([Einddatum]-[Startdatum])}),
    extract = Table.TransformColumns(dList, {"dList", each Text.Combine(List.Transform(_, Text.From), ":"), type text}),
    xMaxList =  List.Transform({
        1..List.Max(
         Table.AddColumn(extract, "Custom", each
            List.Count(
                Text.PositionOfAny([dList], {":"}, Occurrence.All)
            ))[Custom]
        ) +1
    }, each "C" & Text.From(_)),
    lCount = List.Count(xMaxList),
    split = Table.SplitColumn(extract, "dList", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), xMaxList),
    toInt = Table.TransformColumnTypes(split, List.Zip({xMaxList,List.Repeat({Int64.Type},lCount)})),
    result = Table.TransformColumnTypes(toInt, List.Zip({xMaxList,List.Repeat({type date},lCount)}))
in
    result

Hoi JEC,

Bedankt. Helaas nog geen kaas gegeten van Power Query dus zal me daar dan eerst in verdiepen voordat ik dit kan gebruiken

Bedankt!