Refresh Failed in PowerBI but successful in PowerBI Desktop

[chongtsc]

Hello all, 

I have an issue with my refresh and I had try to break the connection source but still not working.  It only failed when I try to refresh in PowerBI service. If I republish it from PowerBI Desktop, everything is good! I check all the source and there no error. I want to check in with community if there any other work around?

Data source error: DataFormat.Error: File contains corrupted data.. ;File contains corrupted data.. The exception was raised by the IDbCommand interface. Table: End_Active.

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?  What is your data source?

[chongtsc]

Hello Ibendlin,

I am trying to fix my refresh issue in PowerBI services. I connected my data source through SharePoint. My file is all all excel which every month I drop multiple file in specific assigned folder since I am dealing with multiple file records.

There is a suggestion that break the source connection and I already tried that. I also check my file to ensure there no protected sheet in the file. 

My goal is trying to fix the refresh in Power BI service without any of the error issue. 

[lbendlin]

Are you using the Sharepoint Folder connector?

[chongtsc]

That is correct, I utilize the SharePoint folder. I have mutiple table connected through that data. 

[lbendlin]

can you show a sanitized version of the power query code?

[chongtsc]

Here my PowerQuery. I am still new in the platform and I am aware is little long. I removed some senstive data and replaced with FileName. Hope this help. 

let
    Source = SharePoint.Contents(FileName, [ApiVersion = 15]),
    #"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
    Metrics = #"Shared Documents"{[Name="Metrics"]}[Content],
    EmployeeData = Metrics{[Name="EmployeeData"]}[Content],
    End_ActiveEmployee = EmployeeData{[Name="End_ActiveEmployee"]}[Content],
    #"Expanded Content" = Table.ExpandTableColumn(End_ActiveEmployee, "Content", {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}, {"Content.Content", "Content.Name", "Content.Extension", "Content.Date accessed", "Content.Date modified", "Content.Date created", "Content.Attributes", "Content.Folder Path"}),
    #"Invoke Custom Function1" = Table.AddColumn(#"Expanded Content", "Transform File (3)", each #"Transform File (3)"([Content.Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1",{"Content.Name", "Transform File (3)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (3)", Table.ColumnNames(#"Transform File (3)"(#"Sample File (3)"))),
    #"Added Custom" = Table.AddColumn(#"Expanded Table Column1", "Status", each "End_Active"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom", "Department Name", "Department Name - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Department Name - Copy", Splitter.SplitTextByDelimiter(")", QuoteStyle.Csv), {"Department Name - Copy.1", "Department Name - Copy.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Department Name - Copy.1"}),
    #"Uppercased Text" = Table.TransformColumns(#"Removed Columns",{{"Department Name - Copy.2", Text.Upper, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Uppercased Text",{{"Department Name - Copy.2", "Department_Name"}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns", "Business Unit", "Business Unit - Copy"),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column1",{{"Business Unit - Copy", "Division_Reformat"}, {"Content.Name", "FileName"}}),
    #"Duplicated Column2" = Table.DuplicateColumn(#"Renamed Columns1", "FileName", "FileName - Copy"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Duplicated Column2", "FileName - Copy", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"FileName - Copy.1", "FileName - Copy.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"FileName - Copy.1", type date}, {"FileName - Copy.2", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"FileName - Copy.2"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns1",{{"FileName - Copy.1", "Date_SourceName"}}),
    #"Duplicated Column3" = Table.DuplicateColumn(#"Renamed Columns2", "Date_SourceName", "Date_SourceName - Copy"),
    #"Renamed Columns3" = Table.RenameColumns(#"Duplicated Column3",{{"Date_SourceName - Copy", "FileName_Date"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns3", "InternStatus", each if [On Leave] = "Yes" and [Employee Type] = "Intern"
then "Yes" else "No"),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "EmployeeType_Reformat", each if [Employee Type] = "Apprentice Permanent" then "Apprentice" else [Employee Type]),
    #"Added Custom2" = Table.AddColumn(#"Added Conditional Column", "DivisionLookup", each [Cost Center]&""&[Division])
in
    #"Added Custom2"

[lbendlin · Accepted solution]

Please post the code for 

Transform File (3)

While it is ok to use the "Combine binaries"  function for a beginner, you have to be extra careful to make sure that all files have exactly the same structure.  It is better to create your own ingestion process instead.

[chongtsc]

Hello @lbendlin 
All my file is the same structure. I did not open or edit anything. Below is my transform file. 

let
    Source = Excel.Workbook(#"End_Active Parameter", null, true),
    Sheet2 = Source{[Name="Sheet1"]}[Data],
    #"Removed Top Rows" = Table.Skip(Sheet2,3),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"LDAP ID", Int64.Type}, {"Local Company Employee Number", Int64.Type}, {"EE Name", type text}, {"FT/PT", type text}, {"Pay Rate Type", type text}, {"Employee Type", type text}, {"Exempt", type text}, {"Hours", Int64.Type}, {"Email Address", type text}, {"Hire Date", type date}, {"Continuous Service Date", type date}, {"Employee Status", type text}, {"Location", type text}, {"Company", type text}, {"Division", type text}, {"Business Unit Code", type text}, {"Business Unit", type text}, {"Department Name", type text}, {"Cost Center", type text}, {"Shift", type text}, {"Supervisory Organization - Primary Location", type text}, {"Manager", type text}, {"Manager ID", Int64.Type}, {"Years in Current Position", type number}, {"Management Level", type text}, {"Job Effective Date", type date}, {"Job Code", type text}, {"Job Profile", type text}, {"Job Family Group", type text}, {"Position Title", type text}, {"Pay Group", type text}, {"Gender", type text}, {"Age", Int64.Type}, {"Age Group", type text}, {"Generation", type text}, {"Race/Ethnicity", type text}, {"EEO Race", type text}, {"EEO Job Classification Code", type text}, {"Hispanic or Latino", type text}, {"Military Service", type text}, {"Disability Statuses", type text}, {"Legal First Name", type text}, {"Legal Last Name", type text}, {"On Leave", type text}, {"Active Status with Date", type text}, {"Active Status", type text}, {"Is Rehire", type text}, {"Original Hire Date", type date}, {"Tenure Category - Position", type text}})
in
    #"Changed Type"

[lbendlin]

Sheet2 = Source{[Name="Sheet1"]}[Data]

That looks ominous.  Can you confirm that each of the Excel files indeed has a sheet named "Sheet1"  ?

[chongtsc]

Hello @lbendlin,

I think I had fixed the issue. I went back to my transform file, redo the step carefully. I think it fix the refresh issue. 

[chongtsc]

Hello @lbendlin, 

I check all the file, is all the Sheet1. 

[SaiTejaTalasila]

Hi @chongtsc ,

On power bi desktop open transform data>turn on column profiler or column quality>select table and check applied steps one by one from source step and check whether you have any errors or not.

If you have any errors then you need to take necessary actions to fix the issue.

Thanks,

Sai Teja 

[chongtsc]

Hello @SaiTejaTalasila , 
It does not seem have any issue. I check each of the step.