cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alicek
Helper II
Helper II

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

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. 

1 ACCEPTED SOLUTION
parry2k
Super User III
Super User III

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

12 REPLIES 12
parry2k
Super User III
Super User III

@alicek good to hear. cheers!!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User III
Super User III

@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)






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

parry2k
Super User III
Super User III

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User III
Super User III

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

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?

 

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

@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.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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"))),

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

@alicek does excel workbook has always one sheet only?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Yep!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors