Problem with PowerBI server refresh: Microsoft SQL: Invalid column name 't0_1'

[Anonymous]

Hello everybody,

since two days the PowerBI dataset on the server which was scheduled to automatically refresh, gives an error:

Refresh failed:

Bronbestand_Medicinfo_data has failed to refresh.
Failure details: The last refresh attempt failed because of an internal service error. This is usually a transient issue. If you try again later and still see this message, contact support.

Microsoft SQL: Invalid column name 't0_1'.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action.

Next refresh for Bronbestand_Medicinfo_data is scheduled for April 30, 2020 11:00 UTC.

This is strange, because so far as I know, nothing has changed. When I refresh the same dataset in PowerBI desktop, then I don't get the error. But when I publish the report to the PowerBI server and I refresh, then I get this error.

Unfortunally the error doesn't say which table has the problem and I have many tables.

Can please somebody help me?

Kind regards,

Nina

[AlexPowers]

@Eliasaki92 can you post your M query by chance? I see this whenever the Query gets too big, might just need to rearrange a few steps - follow my upside down pyramid method too!

https://www.youtube.com/watch?v=9sV3hIn8VTY&list=PLKW7XPyNDgRCorKNS1bfZoAO3YSIAVz3N&index=2&t=6s

[Nese_FZ]

Hi everybody

last release (june) solved this issue, at least in my database.

thanks!!!

[Eliasaki92]

@AlexPowers many thanks for your swift response and the pyramid method. Of course there are ways to improve the current query but it is created by the end user. The big question is, why did it work in Power BI Report Server Desktop January and not in the current May Version of Power BI Report Server Desktop Version?

Here is the pretty simple M script from my colleague. I assume the problem comes from the "sort by step". I am aware that this is step could be deleted but as this worked before I am wondering why it is failing now.

let

    Source = Sql.Database("VSAT-BWDATA", "BWData"),

    dbo_W1_Planta_Project_Ressources = Source{[Schema="dbo",Item="W1_Planta_Project_Ressources"]}[Data],

    #"Renamed Columns" = Table.RenameColumns(dbo_W1_Planta_Project_Ressources,{{"Planned_Effort", "Planned_Effort"}}),

    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Datum", Order.Ascending}}),

    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"EffortGroup"}),

    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Planned_Costs", "Plankosten"}, {"Planned_Effort", "Planaufwand"}}),

    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns1", "Kostengruppe", each if [CostGroup] = "INT-K" then "Internal costs" else if [CostGroup] = "EXT-K" then "External costs" else if [CostGroup] = "SACH-K" then "General costs" else if [CostGroup] = "INV-K" then "Investment" else if [CostGroup] = "BU-K" then "BU/CDiv costs" else if [CostGroup] = "SONST-K" then "Others" else if [CostGroup] = "RENTING-K" then "Renting/Leasing IT" else if [CostGroup] = "BUS-CC" then "Business cost center" else null),

    #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Kostengruppe", type text}}),

    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"PID Planta", "CostGroup", "Kostengruppe", "Datum", "Plankosten", "Planaufwand"}),

    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Kosten", each if [CostGroup] = "INT-K" then [Planaufwand]*65 else [Plankosten]),

    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Kosten", type number}}),

    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Plankosten", "Planaufwand"})

in

    #"Removed Columns1"

 Would be great to here back from you. 

Here also the to query foldings again: 


Many thanks in advance,
Elias 

[gonzna3]

Is there an estimated time frame for a fix????

[AlexPowers]

@Eliasaki92 give this a shot 🙂 - added a few comments. It's all about the step order... follow the upside down triangle of #30DQUERY

let
    Source = Sql.Database("VSAT-BWDATA", "BWData"),
    dbo_W1_Planta_Project_Ressources =
        Source{[
            Schema = "dbo",
            Item = "W1_Planta_Project_Ressources"
        ]}
            [Data],
    #"Renamed Columns" =
        Table.RenameColumns(
            dbo_W1_Planta_Project_Ressources,
            {
                {
                    "Planned_Costs",
                    "Plankosten"
                },
                {
                    "Planned_Effort",
                    "Planaufwand"
                }
            }
        ),
    #"Added Conditional Column" =
        Table.AddColumn(
            #"Renamed Columns",
            "Kostengruppe",
            each
                if [CostGroup] = "INT-K" then
                    "Internal costs"
                else if [CostGroup] = "EXT-K" then
                    "External costs"
                else if [CostGroup] = "SACH-K" then
                    "General costs"
                else if [CostGroup] = "INV-K" then
                    "Investment"
                else if [CostGroup] = "BU-K" then
                    "BU/CDiv costs"
                else if [CostGroup] = "SONST-K" then
                    "Others"
                else if [CostGroup] = "RENTING-K" then
                    "Renting/Leasing IT"
                else if [CostGroup] = "BUS-CC" then
                    "Business cost center"
                else
                    null,
            type text
        ),
    #"Added Custom" =
        Table.AddColumn(
            #"Added Conditional Column",
            "Kosten",
            each
                if [CostGroup] = "INT-K" then
                    [Planaufwand] * 65
                else
                    [Plankosten],
            type number
        ),
    /* 
        Is this necessary? The Data Model loads columns in Alphabetical Order in the fields pane. 
        Reording columns is purely for the visual exploration on the Power Query side.
    */
    #"Reordered Columns" =
        Table.ReorderColumns(
            #"Added Custom",
            {
                "PID Planta",
                "CostGroup",
                "Kostengruppe",
                "Datum",
                "Plankosten",
                "Planaufwand"
            }
        ),
    #"Sorted Rows" =
        Table.Sort(
            #"Reordered Columns",
            {
                {
                    "Datum",
                    Order.Ascending
                }
            }
        ),
    #"Removed Columns" =
        Table.RemoveColumns(
            #"Sorted Rows",
            Table.Sort,
            {
                "EffortGroup",
                "Plankosten",
                "Planaufwand"
            }
        )
in
    #"Removed Columns"

[Anonymous]

I guess this problem again came back in the October edition. Did anyone face this issue yet in the year 2021 for the server edition?

[Nese_FZ]

Hi modsher

Sorry delay an answer. We didn´t experience this issue yet this year.

[mshparber]

I have this issue right now Nov-2022 in Dataflows.

@Aymenbtkh  solution worked for me - I removed on of the sort steps and the problem is gone.

Thanks a lot!