Issues on Consolidating Several Sheets into One Sheet inside a Workbook

[tksnota]

Hi,

I'm Noel and new in this forum.

This is my first post and will not be the last.

My question is why not all the tabs are being captured in power query when I run the Excel.CurrenWorkbook()?

I had 25 tabs in the worksheet and only showed 2 or these 2 are tables?

What do I need to do to use power query to consolidate the data from the 25 tabs without creating new 25 queries and not using the consolidate function?

 

[JGordon11]

Excel.CurrentWorkbook() is going to retrieve the tables and named ranges in the current workbook.

If you want all the data in a saved workbook you can use Excel.Workbook(File.Contents("file path and name here"), null, true).

Alternatively you can name the used range in each sheet and then use the Excel.CurrentWorkbook() function.

Here is a macro that will name the used range of each sheet:

Sub NameSheetData()
    Dim s, rt As String
    For Each s In Worksheets
        rt = "=" & s.Name & "!" & s.UsedRange.Address
        ActiveWorkbook.Names.Add Name:=s.Name & "Content", RefersTo:=rt
    Next
End Sub

 

[tksnota]

thanks. is the one i need to use Excel.Workbook(File.Contents("file path and name here"), null, true) for power query?

i'm really sorry because i'm new to this power query so i have some follow up questions

- is File.Contents a command or the filename itself
- file path meant where it stored. if so can i use the path for shared online drive e.g. one drive and will i be able to autorefresh it?
- is name here the actual file name?
- i need to show some null cells. what do i need to replace the null? or can i just automatically consolidate the data in one sheet with both null and duplicate data?

thanks

 

[JGordon11]

Excel.Workbook - PowerQuery M

Learn more about: Excel.Workbook

learn.microsoft.com

File.Contents - PowerQuery M

Learn more about: File.Contents

learn.microsoft.com

 

[tksnota]

thanks