0
How to create an Excel Table from Multiple HTML Files

Open 1 Answers 42 Views Software and Applications

Here's a quick function to convert Multiple HTML files with Data Table to Excel using power query

Create a Function Query

(Name as text) as table =>
let
    Source = Web.Page(File.Contents("C:\contentpath" & (Name) & ".html")),
    Data0 = Source{0}[Data],
    #"Transposed Table" = Table.Transpose(#"Data0"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table")
in
    #"Promoted Headers1"

Then Create a Table Query to transform HTML files to Table.

let
    Source = Folder.Files("C:html data files\html"),
    #"Sorted Rows" = Table.Sort(Source,{{"Date created", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Reordered Columns",{"Index", "Name", "Date accessed", "Date modified", "Date created", "Folder Path"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns","Name",Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv),{"Name.1", "Name.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Name.1", "Name"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each FUNCTIONNAME([Name])),
    #"Expanded Custom" = Table.ExpandTableColumn....
in  #"Expanded Custom" 

1 Answer

0
Here's a quick video to combine Multiple Excel Files to one using Power Query: https://www.youtube.com/watch?v=a7E29H5ZUmE
answered Sep 13 by michael New Member (1,140 points)
...