Workaround to Key (Sheet Name) changing daily? (SharePoint folder, filtered for earliest file)

[alicek]

Hi all, 

I connect to a SharePoint folder and filter to only take the most recent created file. This works brilliantly, as there is a daily drop into the folder from an external source and I only want the most recently dropped file. The column names are always exactly the same and there is always only a single sheet.

HOWEVER, the only difference is that the name of the tab in the excel file (the sheet name) changes to be the date. Therefore, I am getting an update/refresh error that the key cannot be found, because the key is the sheet name and it is changing daily. 

Does someone know of a way to automatically rename the key or the sheet name within the transformation steps, so that this process will still continue?

Thank you! Let me know if you need more screenshots of examples. 

[parry2k]

@alicek good to hear. cheers!!

[parry2k]

@alicek it should, seems like I'm missing something, even if you share pbix it will not work because files are on SharePoint, we can do a Teams/Zoom meeting for me to look at it. you can reach out directly via email (it is in my signature)

[alicek]

It actually worked when I started over from scratch! Thank you so much @parry2k ! Marked as solution

[parry2k]

@alicek no other change required.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@alicek this is where you need to make the change:

let
Source = Excel.Workbook(Parameter1, null, true),
SheetName = Source{0}[Name],
#"Shipping Details as of 20210207_Sheet" = Source{[Item=SheetName,Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Shipping Details as of 20210207_Sheet", [PromoteAllScalars=true])
in
#"Promoted Headers"

Line after Source step is a new line to get the sheet name and next line now uses this Sheetname rather than a fixed value.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[alicek]

Thank you @parry2k !! 

For thes two lines:

#"Shipping Details as of 20210207_Sheet" = Source{[Item=SheetName,Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Shipping Details as of 20210207_Sheet", [PromoteAllScalars=true])
in

, the step name still references the original tab name (#"Shipping Details as of...") - do I need to make changes to those or with this new line, it will auto-update to be correct?

[alicek]

It unfortuantely is not working or auto-updating, @parry2k 😞 You've gotten me so close, thank you! Let me know if you have an idea how to fully fix it.

[parry2k]

@alicek ok then it is pretty straight forward, it will be easier if you just copy the M code by clicking the advanced editor and I will send the script back to you.

[alicek]

Thanks @parry2k !

This is the code for the table itself, but there's also all those files that occur to do the transformation. Let me know if you need those -- there's not really much M code int he advanced editor for those that I can see. 
let
Source = SharePoint.Files("https://ccc.sharepoint.com/sites/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://ccc.sharepoint.com/sites/02. Data and Datasets/001 Administered, Allocated, Shipped/Shipping Files_Shipment details to date/February 2021/" or [Folder Path] = "https://ccc.sharepoint.com/sites/02. Data and Datasets/001 Administered, Allocated, Shipped/Shipping Files_Shipment details to date/January 2021/")),
#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Content", "Name", "Extension", "Date accessed", "Date modified", "Folder Path", "Date created", "Attributes"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns", "Folder Path", "Folder Path - Copy"),
#"Reordered Columns1" = Table.ReorderColumns(#"Duplicated Column",{"Content", "Name", "Extension", "Date accessed", "Date modified", "Folder Path", "Folder Path - Copy", "Date created", "Attributes"}),
#"Replaced Value" = Table.ReplaceValue(#"Reordered Columns1","https://ccc.sharepoint.com/sites/02. Data and Datasets/001 Administered, Allocated, Shipped/Shipping Files_Shipment details to date",Replacer.ReplaceText,{"Folder Path - Copy"}),
#"Filtered Rows1" = Table.SelectRows(#"Replaced Value", each ([Extension] = ".xlsx")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Date created", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
#"Filtered Hidden Files1" = Table.SelectRows(#"Kept First Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),

[alicek]

Here it is for the Transform Sample File: 

let
Source = Excel.Workbook(Parameter1, null, true),
#"Shipping Details as of 20210207_Sheet" = Source{[Item="Shipping Details as of 20210207",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Shipping Details as of 20210207_Sheet", [PromoteAllScalars=true])
in
#"Promoted Headers"

[parry2k]

@alicek does excel workbook has always one sheet only?

[alicek]

Yep!