How to apply transformation to multiple tables in the which is as small as making first row as head

[Harsh_]

Hello everyone, It's my first post here glad to see all the support in the other posts.

I can see that there is a similar discussion already going on related to applying the same transformation to multiple tables using the advanced editor, But let's say we have 10 tables on the left-hand pane I don't see any option to select all at once, also there is this option at the top to "make the first line as header". I just wanna use that for all tables, there shouldn't be a need to open the advanced editor I guess. I am new to the software, please help me with this. 

Adding this image for reference, I tried to select all the tables by ctrl+select but, still it does the operation on one table only.

[danextian]

hI @Harsh_ 

Assuming those tables have the same structure, they all must be in the same query to be able appy the same set of transformations at once. 

It seems you're trying to scrape data from webpages but instead of connecting to them in the same query, you are with multiple ones. In the screenshot below, there is a column called html with columns that need to be pivoted. Transformed Table column contains the pivote versions of those tables.

Here's a sample M Script. Take note that instead of using a concatenated based url + webpages in the connection string, I am using relative path and othere query parameters available for Web.Contents. This is so  I avoid creating a dynamic data source which will prevent the query from being refreshed in the service.

let
    Source = Table.FromColumns({{30..40}}, {"Page"}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Page", Int64.Type}}),
    #"Extracted Security Information Table" = Table.AddColumn(#"Changed Type", "Html", each let
    cmpyId = [Page],
      // Example company ID
    binaryfile = 
    Web.Contents(
        "https://edge.pse.com.ph",  // Base URL
        [
            RelativePath = "companyInformation/form.do",  // Relative path
            Query = [cmpy_id = Text.From(cmpyId)]  // Query parameter
        ]
    )

    in  Html.Table(binaryfile, {{"Column1", "TABLE.view:nth-child(2) > * > TR > :nth-child(1)"}, {"Column2", "TABLE.view:nth-child(2) > * > TR > :nth-child(2)"}}, [RowSelector="TABLE.view:nth-child(2) > * > TR"])),
    #"Transformed Security Information Table" = Table.AddColumn(#"Extracted Security Information Table", "Transformed  Table", each let tbl = [Html]
in
Table.Pivot(tbl, List.Distinct(tbl[Column1]), "Column1", "Column2")),
    #"Removed Columns" = Table.RemoveColumns(#"Transformed Security Information Table",{"Html"}),
    #"Expanded Transformed  Table" = Table.ExpandTableColumn(#"Removed Columns", "Transformed  Table", {"Sector", "Subsector", "Corporate Life", "Incorporation Date", "Number of Directors", "Stockholders' Meeting as per By-Laws", "Fiscal Year", "External Auditor", "Transfer Agent"}, {"Sector", "Subsector", "Corporate Life", "Incorporation Date", "Number of Directors", "Stockholders' Meeting as per By-Laws", "Fiscal Year", "External Auditor", "Transfer Agent"})
in
    #"Expanded Transformed  Table"

[Harsh_]

@rajendraongole1 Hi there!

Thanks for your prompt response, I really appreciate your great work on the community.

[rajendraongole1]

Hi @Harsh_ -Power Query treats each table as a separate query, and transformations are applied at the query level. The UI doesn't currently support batch processing for multiple queries in the pane. Using Advanced Editor or manual methods is necessary for batch operations.

Unfortunately, in Power Query, there's no direct way to apply transformations (like making the first row as headers) to multiple tables simultaneously through the UI without using code in the Advanced Editor. Each table must be transformed individually unless you apply a programmatic solution.

you have to use manual process only:

By selecting each table:
Open the query in Power Query Editor.Go to the Home tab.Click the "Use First Row as Headers" button.Repeat for each table.

Hope this helps.