Forum Discussion
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_VermaMost 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.
- AnonymousNot 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_VermaMost 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.