Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
alicek
Helper III
Helper III

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

@alicek good to hear. cheers!!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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

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

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

@alicek does excel workbook has always one sheet only?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.