Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

DataFormat.Error when using Table.ExpandListColumn()

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

7 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable

      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's avatar
        Vijay_A_Verma
        Most Valuable Professional

        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.