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

Rory, Hi Good morning. Noted and Thank You.

I have a super idea on how to improve Microsoft Community.

I propose I post the question as I did and then we schedule a Teams call so I can share my screen. We briefly dialogue, this will ensure you aren’t wasting your time resolving something else, during the call I copy\paste your answer into my file and then afterwards you post the answer to my question, I mark it as resolved and you still receive credit.

This will go a long way to answering questions faster on the 1st attempt and still the whole Community benefits from seeing the answer.

Thank You for your consideration and let me know your availability so we can schedule the brief Teams call. Greatly appreciated!

Answer 4

I cannot understand what you want.

let fnPromoteHeaderRows = (Source as table, Nth as number) =>
let
Part = Table.Range(Source, 0, Nth),
NewHeaders = List.Transform(Table.ColumnNames(Part), each Text.Combine(Table.Column(Part, _)," ")),
OldHeaders = Table.ColumnNames(Source),
Result = Table.RenameColumns(Table.Skip(Source, Nth), List.Zip({OldHeaders, NewHeaders}))
in
Result
,
documentation = [
Documentation.Name = "fnPromoteHeaderRows",
Documentation.Description = "Promotes Nth rows of values as new headers",
Documentation.LongDescription = Documentation.Description,
Documentation.Category = "Table",
Documentation.Source = "******@gmx.net",
Documentation.Version = "1.0",
Documentation.Author = "Andreas Killer",
Documentation.Examples = {[
Description = "
fnPromoteHeaderRows(Table1, 2)
",
Code = "
Source : The table to process
Nth : The number of top rows to be combined and promoted as headers
",
Result = ""]}]
in
Value.ReplaceType(fnPromoteHeaderRows, Value.ReplaceMetadata(Value.Type(fnPromoteHeaderRows), documentation))

Answer 5

Hi,

I cannot understand what you want. Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.