How to combine SharePoint List from multiple SharePoint sites

try this one. 

let
    // Get the list of SharePoint sites and corresponding list names
    Source = SharePoint.Tables("https://main.sharepoint.com", [ApiVersion = 15]),
    SitesList = Source{[Name="SitesList"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(SitesList,{{"Site URL", type text}, {"List Name", type text}, {"Tenant Name", type text}, {"Site Code", type text}}),

    // Add a custom function to fetch data from the corresponding SharePoint list for each SharePoint site
    FetchList = (site as text, list as text) => 
        let
            Source = SharePoint.Tables(site, [ApiVersion = 15]),
            List = Source{[Name=list]}[Content]
        in
            List,

    // Add a new column "Custom" to call the custom function for each row
    AppendLists = Table.AddColumn(#"Changed Type", "Custom", each FetchList([Site URL], [List Name])),

    // Expand the tables from the "Custom" column into rows
    #"Expanded Custom" = Table.ExpandTableColumn(AppendLists, "Custom", {"Employee ID", "Name", "Position"}, {"Employee ID", "Name", "Position"}),

    // Select only the necessary columns
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Tenant Name", "Site Code", "Employee ID", "Name", "Position"})
in
    #"Removed Other Columns"

This script will:

1. Fetch the list of SharePoint sites and their corresponding list names from your main SharePoint site.
2. For each SharePoint site, it fetches the corresponding SharePoint list.
3. It then expands each fetched SharePoint list into rows and appends these rows into the main table.
4. Finally, it selects only the necessary columns.

Please replace "Employee ID", "Name", "Position" with your actual column names in the SharePoint Lists. If your lists have different structures, you'll need to modify the script accordingly to accommodate those differences.

Also, please replace "https://main.sharepoint.com" with your main SharePoint site URL and "SitesList" with your actual list name that contains the SharePoint site URLs and corresponding list names.

this is an experimental solution. you need to adjust it.