Change the first value (0,0) of all tables in a column

[T1T1K]

Hi,

I have a list of files I want to read from a folder, which gives me a column made of tables ready to expand.

Only all my headers are in the second row, the first one being the tittle. When expending, all columns are correctly concatenated, except the first one, as the title being different for all tables make only the first table readed, all others are "null" values . 
(Same issue as describred here : https://stackoverflow.com/questions/53956748/power-query-missing-values-when-expanding-tables )

I thus wonder if it is possible to delete the first row or change the first value of the first row of all tables in the column before expanding, in order not to lose my data. 

Thank you,

T1T1

[Royel]

Hi, @T1T1K 

I find this problem interesting, and I’d like to solve it on my end. Here’s how I approached it:

Please take a look at the three sample files I’ve prepared.

File1

Sales Report January
Date	Product	Amount
1/1/2024	Product A	1000
1/2/2024	Product B	1500
1/3/2024	Product C	800

File 2: 

Revenue Summary February
Date	Product	Amount
2/1/2024	Product A	1200
2/2/2024	Product B	1800
2/3/2024	Product C	900

File 3: 

Monthly Analysis March
Date	Product	Amount
3/1/2024	Product A	1100
3/2/2024	Product B	1600
3/3/2024	Product C	950

I have loaded these three sample files as csv in a folder. 

And here is the power query code: Update file location

let
    Source = Folder.Files("C:\....\"),
    FilteredRows = Table.SelectRows(Source, each [Extension] = ".csv"),
    
    // Add custom column to read CSV content
    AddContent = Table.AddColumn(FilteredRows, "Contents", 
        each Csv.Document([Content], [Delimiter=",", Encoding=65001])
    ),
    
    // Transform the Content column to skip first row (title row)
    SkipTitleRow = Table.TransformColumns(
        AddContent,
        {
            {"Contents", each Table.Skip(_, 1), type table}
        }
    ),
    
    // Now expand the Content column
    ExpandContent = Table.ExpandTableColumn(
        SkipTitleRow, 
        "Contents", 
        {"Column1", "Column2", "Column3"}, 
        {"Date", "Product", "Amount"}
    ),
    
    // Clean up - keep only necessary columns
    SelectColumns = Table.SelectColumns(ExpandContent, { "Date", "Product", "Amount"}),
    #"Sorted Rows" = Table.Sort(SelectColumns,{{"Amount", Order.Descending}}),
    #"Removed Top Rows" = Table.Skip(#"Sorted Rows",2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Product", type text}, {"Amount", Int64.Type}})
in
    #"Changed Type"

Output:

 Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

[v-hjannapu]

Hi @T1T1K  ,
I would also take a moment to thank @jgeddes , for actively participating in the community forum and for the solutions you Have been sharing in the community forum. Your contributions make a real difference.

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Harshitha.

[v-hjannapu]

Hi @T1T1K,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.


Regards,
Harshitha.

[v-hjannapu]

Hi @T1T1K,
I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.

Regards,
Harshitha.

[v-hjannapu]

Hi @T1T1K  ,

I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.

Regards,
Harshitha.

[v-hjannapu]

Hi @T1T1K,
I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.

Regards,
Harshitha.

[jgeddes]

Table.TransformColumns can be used here. You can skip the first row of the nested tables prior to expanding the column.
Example:

let
    Source = 
    #table(
        type table [Transformer le ficher = table],
        {
            {
                #table(
                    type table [Column1=text],
                    {
                        {"title1"},
                        {"value I want to keep"}
                    }
                )
            },
            {
                #table(
                    type table [Column1=text],
                    {
                        {"title2"},
                        {"second value I want to keep"}
                    }
                )
            }
        }
    ),
    Custom1 = 
    Table.TransformColumns(
        Source, 
        {
            {"Transformer le ficher", each Table.Skip(_,1), type table}
        }
    ),
    #"Expanded Transformer le ficher" = 
    Table.ExpandTableColumn(
        Custom1, 
        "Transformer le ficher", 
        {"Column1"}, 
        {"Column1"}
    )
in
    #"Expanded Transformer le ficher"