DataFormat.Error when using Table.ExpandListColumn()

[Marting86]

Hello,

I have here a skript which generally seems to work but the step #"expandMonthList" creates a format error when loading the Data in the model:

When taking out the step #"expandMonthList" the data load works. 

Here my Skript:

let
    Quelle = Folder.Files("C:\Users\gemagrz\Saved Games\Desktop\OSBSTD"),
    #"Benutzerdefinierte Funktion aufrufen1" = Table.AddColumn(Quelle, "Datei transformieren", each #"Datei transformieren"([Content])),
    #"Umbenannte Spalten1" = Table.RenameColumns(#"Benutzerdefinierte Funktion aufrufen1", {"Name", "Source.Name"}),
    #"Andere entfernte Spalten1" = Table.SelectColumns(#"Umbenannte Spalten1", {"Source.Name", "Datei transformieren"}),
    #"Erweiterte Tabellenspalte1" = Table.ExpandTableColumn(#"Andere entfernte Spalten1", "Datei transformieren", Table.ColumnNames(#"Datei transformieren"(Beispieldatei))),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Erweiterte Tabellenspalte1", each true),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Gefilterte Zeilen",{{"Invoice date", type text}}),
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Geänderter Typ",{{"Invoice date", type date}}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ1",{{"Line amount  local c", "Net Invoiced Sales"}}),
    #"Geänderter Typ2" = Table.TransformColumnTypes(#"Umbenannte Spalten",{{"Planned start date", type text}, {"Planned finish date", type text}}),
    chgTypes = Table.TransformColumnTypes(#"Geänderter Typ2",{{"Planned start date", type date}, {"Planned finish date", type date}, {"Net Invoiced Sales", type number}}),
    #"Neueste eingefügt" = Table.AddColumn(chgTypes, "Neueste", each List.Max({[Planned start date], [Invoice date]}), type date),
    #"Neueste eingefügt1" = Table.AddColumn(#"Neueste eingefügt", "Neueste.1", each List.Max({[Planned finish date], [Invoice date]}), type date),
    #"addSalesPeriod" = Table.AddColumn(#"Neueste eingefügt1", "SalesPeriod", each
        List.Distinct(
            List.Transform(
                {Number.From([Neueste]).. Number.From([Neueste.1])},
                each Date.EndOfMonth(Date.From(_))
            )
        )
    ),
    #"addSplitInvoicedSales" = Table.AddColumn(#"addSalesPeriod", "SplitContractInvoicedSales", each [Net Invoiced Sales] / List.Count([SalesPeriod])),
    #"expandMonthList" = Table.ExpandListColumn(#"addSplitInvoicedSales", "SalesPeriod"),
    #"Geänderter Typ3" = Table.TransformColumnTypes(expandMonthList,{{"SplitContractInvoicedSales", type number}, {"SalesPeriod", type date}, {"Total discount amoun", type number}, {"Charge value", type number}})
in
    #"Geänderter Typ3"

Hope someone can give me a hint. I am stucked here for 2 days now.

Cheers

Martin

[Vijay_A_Verma]

Do following -

At the bottom, you see this kind of message

Click the message starting with Column profiling and change it to "Column profiling based on entire data set".

Now, check the column quality for this column and you should notice error.

To know what this error is, select the column - Home menu - Keep rows - Keep errors

You will find the rows with errors. Then you can click error and see error message and correct it.

Note - Since, you are using language other than English, you will need to translate it appropriately.

[Marting86]

Hi,

unfortunately I cant find this message anywhere. I just get the error message. No error report or anything else indicating which coulumn is causing the problem. Looking on the column quality power query its showing nowhere an error.

[Vijay_A_Verma]

Since you are expanding SalesPeriod column, hence error message you should get in that column. But before that make sure column profiling based on entire data set is set. 

[Marting86]

When I change the setting to "column profiling on entire data set" i get an unexpected error 😓

[Vijay_A_Verma]

Click on one of the errors and post the message text (not picture so that I can use Google translate). 

[Marting86]

there is no more message text. When I change the setting it starts computing, then it stops and only displays as on my screenshot. Maybe my computer just cant process the the profiling based on the entire data?

[Vijay_A_Verma]

Insert one index column.

Apply filter for <=2000

Then change the column profiling setting.

Then check for error message.

If error doesn't come, apply filter >2000 and less than 4000...And so on to check for error message in chunks of 2000. When you first encounter it, then do the analysis.