I know Excel formulas very well, and am pretty good at VBA, but I don't know Power Query really at all. I get information from a file on our OneDrive for Business server through Power Query.
Here is the M Code:
let
Source = Excel.Workbook(Web.Contents("https://zraNotReal.sharepoint.com/sites/Test/Shared%20Documents/General/Huddles/TAM%20Huddle%test.xlsm"), null, true),
#"February 2023_Sheet" = Source{[Item="February 2023",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"February 2023_Sheet",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type text}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type number}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}})
in
#"Changed Type"
What I would like to do is replace the file name (TAM%20Huddle%test.xlsm, OR the whole string starting with https://...) and sheet name with a variable that I could set in my workbook.
For instance, I could name cell A1 as "MyFileName" and cell A2 as "MySheet" and have the name of the file and sheet respectively in those cells. In essence, what I want is something like this:
let
Source = Excel.Workbook(Web.Contents(MyFileName), null, true),
#"February 2023_Sheet" = Source{[Item=MySheet,Kind="Sheet"]}[Data]...
But don't know the proper syntax
Moderator Comment: I have updated your post to include Code tags. Please employ them in the future.
Bookmarks