Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Marting86
Helper I
Helper I

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:

 

Marting86_0-1651473758069.png

 

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

 

Marting86_1-1651473758246.png

 

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 7
Vijay_A_Verma
Super User
Super User

Do following -

At the bottom, you see this kind of message

3 - Copy.png

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.

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.

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. 

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

 

Marting86_0-1651487140344.png

 

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

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?

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors