Видео

Can you provide more information about how this named range is defined? If it shows up on the list with Excel.CurrentWorkbook() it is doable.

Hi! Thanks for reaching out. I was able to figure it out. For clarity, I created a dynamic method to pull from a specific data folder and the directory is saved in a defined name of FolderPathLocation. The “Refers To” references the path string and not a cell, range, table or worksheet. If the data folder moves, the user can run VBA to update it. Power Query won’t find it if directly referenced unless it is used within a name defined cell, range, or table. The M code below didn’t work on direct pull of FolderPathLocation, but when I did an =[FolderPathLocation] in Cell M11, and defined M11 as FolderPath it worked let SourceWorkbook = Excel.CurrentWorkbook(), FolderPath = SourceWorkbook{[Name="FolderLocation"]}[Content]{0}[Column1], Source = Folder.Files(FolderPath), FilteredFiles = Table.SelectRows(Source, each [Extension] = ".csv"), ImportData = Table.Combine(FilteredFiles[Content]) in ImportData

I agree this is hard to follow. Basically it's doing a find/replace in bulk to replace the numbers with the words, and I used Table.Buffer so the calculations would refresh properly. Just using Excel would be a good tool for making Bingo cards with the Excel 365 sort features and XLOOKUP. This video also does the Bulk Find Replace: ruclips.net/video/Sgc6QjbH2Ss/видео.html

Ha, thanks. I just ideas of what topics to cover as there are so many other good channels already.

@@josh_excel regarding visualization in excel and nested power pivot and using and 2 new formula "group by" and "pivot by" and python in excel too but this is my point of view.

The issue is that the formats are different for the two durations, so we'll first need to make formats look the same, then use the change type function to convert to duration. There are different ways to approach this, but here I use the Text.Length function to count the number of colons and if there is just 1, I add 00: to the front to represent the missing hours: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Added Custom" = Table.AddColumn(Source, "Colon_Count", each Text.Length([Text time]) - Text.Length(Replacer.ReplaceText([Text time],":",""))), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [Colon_Count] = 1 then "00:" & [Text time] else [Text time]), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type duration}}) in #"Changed Type"

Here are some videos from my friend: ruclips.net/p/PL5NlUYOM2iOhZOJL_GM6zNd2k9-IEqmaV&si=Cmw9wEgkRFw1km_H

If we do a Group by to put data into tables and apply the same methodology it should work. I'll figure it out and put up a new video.

The video comments has the code, but if having the file helps, it's saved here on Google_Drive: docs.google.com/spreadsheets/d/15bBK8wYvUDGfYAO0qo0wyq2NbIKEKci2/edit?usp=sharing&ouid=111698238806896954800&rtpof=true&sd=true

@@josh_excel Thank you, I can learn alot from this file

It's just a blank table with blank headers and one blank row. If you wanted a table with one row that has the number 1, it would be: =#table({"Col1"},{{1}})