Replace Data In PowerQuery

Question

Hi.

I am using “Get Data” > “From File” > “From Excel Workbook” and PowerQuery is pulling it in as a Sheet, in other words, there is no Table in it.

I want to place the value of the non-blank cells in Row 1 into the cell directly below it in Row 2. Column 16 Company replaces Item Code, Column 17 Company replaces Item Code, and so forth. Once completed, I will remove Row 1 and Promote Row 2 to Column Headers.

* I added an Index Column starting with "1"

* Columns 1 - 15 will always have blank cells in Row 1

* Item Code will always be the value I am replacing in Row 2

* I am using the formula Table.ColumnNames to create a list of Column Names from the Excel file because the number of Companys which could appear will change when the report is generated each time.

Thank you in advance for your assistance!

Answer 1

Glad it worked!

No, I didn't use the index in my code (or the ColumnNamesList step).

Answer 2

Ah, I forgot you added an Index column, so the data in the last column is a number, not text. Change it to this:

let

Source = Excel.Workbook(File.Contents("For security\privacy I have removed the url"), null, true),

Product_Details_Sheet = Source{[Item="Product_Details",Kind="Sheet"]}[Data],

ColumnNamesList = Table.ColumnNames(Product_Details_Sheet),

DeleteBlank2ndRow = Table.RemoveRows(Product_Details_Sheet,1,1),

Index = Table.AddIndexColumn(DeleteBlank2ndRow, "Index", 1, 1, Int64.Type),

Rec1 = Record.ToList(Index{0}),

Rec2 = Record.ToList(Index{1}),

NewColumnNames = List.Transform(List.Zip({Rec1, Rec2}), each if _{1} = "Item Code" then Text.From(_{0}) else Text.From(_{1})),

Output = Table.Skip(Table.RenameColumns(Index, List.Zip({Table.ColumnNames(Index), NewColumnNames})), 2)

in

Output

Answer 3

There will be a step in your power query code that is returning a table. That's what I meant. If you're pulling in from another location, your Source step will be different, but the principle will be the same.

Answer 4

Rory, Thank You for replying.

I am using “Get Data” > “From File” > “From Excel Workbook” and PowerQuery is pulling it in as a Sheet, in other words, there is no Table in it.

Here's one way (change Source to whatever your current table step is):

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Rec1 = Record.ToList(Source{0}),

Rec2 = Record.ToList(Source{1}),

NewColumnNames = List.Transform(List.Zip({Rec1, Rec2}), each if _{1} = "Item Code" then _{0} else _{1}),

Output = Table.Skip(Table.RenameColumns(Source, List.Zip({Table.ColumnNames(Source), NewColumnNames})), 2)

in

Output

Answer 5

Here's one way (change Source to whatever your current table step is):

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 

Rec1 = Record.ToList(Source{0}), 

Rec2 = Record.ToList(Source{1}), 

NewColumnNames = List.Transform(List.Zip({Rec1, Rec2}), each if \_{1} = "Item Code" then \_{0} else \_{1}), 

Output = Table.Skip(Table.RenameColumns(Source, List.Zip({Table.ColumnNames(Source), NewColumnNames})), 2) 

in

Output