Scheduling and Manual Refresh issue

[Saxon10]

Data:

Every 4 hours SAP automatically generate report within the same Excel file. The report name called “Data”. The “Data” file contain 4 columns are Log Column, Item, Country Code and Fruits Name. The following columns are Item, Country Code and Fruits Name always same format expect the log column. The log column keep changing every 4 hours.

Error during scheduling and manual refresh.

The Power Bi file contain old data(yesterday data) but the actual Excel file contain latest data. When I try to refresh the report I am getting the following error message “The column 'The log info-Records extracted - 20 Date and Time - 26/01/2021 12:46:01 Execution Time - 0:02:13' of the table wasn't found”.

Old Log Column

The column 'The log info-Records extracted - 20 Date and Time - 26/01/2021 12:46:01 Execution Time - 0:02:13' of the table wasn't found.

New Log Column

The log info-Records extracted - 20 Date and Time - 26/01/2021 16:46:01 Execution Time - 0:02:13

Result

How can I ignore the avoid the particular column error while scheduling the report and manual refresh?

Data connect file path:

Herewith attached the file for your reference https://www.dropbox.com/scl/fi/n838kjomao1ks13xxvsty/SALES-DATA.xlsx?dl=0&rlkey=msgozu0gw825q00e3mpt...

https://www.dropbox.com/s/j44q7shmph0szdy/AUTO%20REFRESH1.pbix?dl=0

[mahoneypat]

The error is caused because you specifically reference to the column name of the original file in the #"Changed Type" step of your SALE query.  Please see this video for how to first rename it using a relative reference using Table.ColumnNames(#"Previous Step"){0} or something like that.  This approach will rename the first column whatever it is called to some new name that is then referenced w/o error in later steps.

Power BI - Use relative references to avoid combine & transform errors - YouTube

Pat

[Saxon10]

Hi,

Thanks for your reply and help and advise.

I am trying to figure out based on the video but I could not find any solutions. I am very new for power query. Can you please advise how can I modify the particullar column.

[mahoneypat]

I couldn't refresh against your files, so I couldn't provide a detailed M solution for you to paste into the query editor.  If you can provide 2 example/mock files (via link to Google Drive, OneDrive, etc.), I can do so.  Please "@" me in the response so I make sure to see it.

Regards,

Pat

[Saxon10]

Here I shared the folder for your reference and I hope you can open the file from here.

https://drive.google.com/drive/folders/1brBg0XnCsDyt2lrTNZCLq5fKvsLqdCRv?usp=sharing

Could you please let me know if any issue?

[Saxon10]

@mahoneypat 

Thanks for your reply and sorry for the late respones.

Herewith attached files (Excel and PBI) for your reference. Could you please let me know if any issue.

https://drive.google.com/file/d/1ioQIDcOpY8vhZ6hrc0pf7zMKkqxpubNI/view?usp=sharing

https://drive.google.com/file/d/1XuIpAAFyzqva-dBYelkNX2zF_XdCUmYh/view?usp=sharing

[mahoneypat]

Looks like you shared just one Excel file, so I did a save as and changed the datetime info in the first cell to recreate the error you are seeing.  Below is a modified query.  Open the advanced editor for your SALES query and replace the text there with this.  You'll need to replace the C:\Test with your actual folder path too.

I made two changes to the final two steps of that query.  The first is List.RemoveFirstN(..., 1) to avoid expanding that first column (that you remove in the next step anyway).  The second was to remove reference to that column in the Remove Columns step.

let
    Source = Folder.Files("C:\Test"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", List.RemoveFirstN(Table.ColumnNames(#"Transform File"(#"Sample File")),1)),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table Column1",{"Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"})
in
    #"Removed Columns"

Pat

[Saxon10]

Hi,

Here is I marked in red box were is difference in-between two data source (Fille path and share point)

When I try to added List.RemoveFirstN(Table.ColumnNames(#"Transform File"(#"Sample File")),1)), after the (#"Sample File (2)"))) then I am receving the following error "Token Equal expected"

#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNams(#"Transform File (2)"(#"Sample File (2)"))),

[v-shex-msft]

Hi @Saxon10,

Did mahoneypat 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements to find it more quickly.

If these also not help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Saxon10]

Thanks for reminder and sorry for the late reply. I am doing some testing that's the reason I can't respond on time. 

[Saxon10]

Hi,

Herewith is the data source of sharepoint file folder path and could you please advise where I need to modify the query to avoid expanding the log column. It's slightly different comparing to folder file path data source. I try to follow-up the same approach but I am receving the error.

Could you please advise.

Sahre Point Data source:

let
source = sharepoint.Files("http:",[Apiversion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "http://")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows" , each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNams(#"Transform File (2)"(#"Sample File (2)"))),
in

#"Removed Columns"

[mahoneypat · Accepted solution]

There are a few ways to get there.  Here is one.  Replace your query with this in the advanced editor, putting in your site url and folderpath strings.  This one combines the files and then filters out the headers from the later files (<> "Item"), and renames the columns.

let
Source = SharePoint.Files("https://", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Filled Down" = Table.FillDown(#"Expanded Table Column1",{"Column1"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
#"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([Item] <> "Item")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"SALES DATA.xlsx", "Filename"}, {"The log info-Records extracted - 20 Date and Time - 26/01/2021 17:46:01 Execution Time - 0:02:13", "Log Datetime"}}),
#"Extracted Text Between Delimiters" = Table.TransformColumns(#"Renamed Columns", {{"Log Datetime", each Text.BetweenDelimiters(_, "Time - ", " Execution"), type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Extracted Text Between Delimiters",{"Filename", "Log Datetime", "Item", "Country Code", "Fruits Name"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Item", Int64.Type}, {"Country Code", type text}, {"Fruits Name", type text}})
in
#"Changed Type"

Pat

[Saxon10]

@mahoneypat,

Thanks for your help and modify the M query.

I tested and its working well for connecting Excel data via folder file path mode.

How and where should I modify the M language query when I connect the data via share point folder files path location? Can you please advise when you have time.

Data source snapshot of share point file path location. 

 

I trying to modify your logic but receiving the following error "Token Equal expected"

[Saxon10]

Sorry for the inconvenience. I am struggling share the files

Thank you so much for your reply and help. I will replicated the same scenario at my end and update the feedback to you. Sorry for the inconvenience. I am struggling to share the files.