Find the Column Header of the Column (change from one table to another ex in Tbl “Colmn11” in Tbl2 “

[DSR]

In Power Query

Hello, I appreciate your help:

I have 26 tables: Tbl1, Tbl2,....tbl26

I want to find the position of a text, assume "FICA" in what Column and in what Row and change the header of that Column and the first Row text of that Row.

The TEXT "FICA" change in position from one table to another. ex. in Tbl1 it is in Clolumn11 and in Tbl2 "ICA" is in Column19 ..etc..

Questions:

Q1 - Find the Column Header of the Column and Row Number (change from one table to another ex in Tbl1 “Colmn11” in Tbl2 “Column19”) that Contains Specific text “FICA”

Q2 - if column contains specific text  “FICA” , rename that column header to “PrlItem” else keep it, rename the First Column text in that Row.

Thank You

[DSR]

I understand it. still the undersore _ reference the list row CN cell

I clicked on Accept, it went to page "no match"

QA3 - Can you reference Column position by number (1,2,3,...19)

QA4 -delete Columns above that Column Number (14)

QA5 - Column headers of the largest amount and SECOND largest amount in a specific row?

Thank you

[Anonymous]

Share an Excel file with some data and show the expected result.

Maybe then I can see what you want to achieve and whether it is possible.

Andreas.

[Anonymous]

I don't understand Q1. Solution for Q2 is below.

Andreas.

let
  Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
  // Get all column names
  CN = Table.ColumnNames(Source),
  // Search all column for "FICA", return column name if found otherwise null, finally remove all nulls
  ThisColumn = List.RemoveNulls(
    List.Transform(CN, each
      if List.Contains(Table.Column(Source, _), "FICA") then _ else null
    )
  ),
  Result = 
    if List.Count(ThisColumn) > 0 then 
      // Rename column if found
      Table.RenameColumns(Source, {{ThisColumn{0}, "PrlItem"}}) 
    else
      Source
in
  Result

[DSR]

Dear Andreas,

Thank you. It works. I want to accept the Solution, but I still have an added questions, I am afraid that if will not get an answer to the added questions:

QA1 - Row of the "FICA"?

QA2 - First Row of largest amount?

QA3 - Column headers of the largest amount and second largest amount in row

What do you suggest shall I accept now and re Publish another one?

Thank you again

DSR

[DSR]

When I tried to understand every step (by applying each step in sequence but separate the following did not work, giving:

Table.ToColumns:

Table.Column(table as table, column as text) as list

 requires: column as text while the underscore _ in the formula: Table.Column(
Source,_) references to what?