Add Value to a Table

Power Query
1

I have PQ that extracts the names of Data Files to be queried for Load and Transform. the table has one Column “Name”

I want to add a row to the list and record the Value “PAR” on it.

Do I used a Table ? - .InsertRows - if so how?

Thanks

let
Source = #“Folder Parameter”,
#“Filtered Rows” = Table.SelectRows(Source, each not Text.Contains([Name], “$”)),
#“Removed Other Columns” = Table.SelectColumns(#“Filtered Rows”,{“Name”})
in
#“Removed Other Columns”

2

Hi @AllisterB,

As I understand it you want to add a record to a single column table with the text value = “PAR”
Yes you can use Table.InsertRows for that but there are other methods as well. The first code below creates an empty table and then inserts that row on top .

let
    Source = #table({"Name"}, {}),
    InsertRow= Table.InsertRows(Source, 0, {[Name = "PAR"]})
in
    InsertRow

And for example this code creates an empty table and appends a table with a single record.

let
    Source = #table({"Name"}, {}),
    CombineTables = Table.Combine({Source, Table.FromRecords({[Name = "PAR"]})})
in
    CombineTables

.

Translated to your sample code this would look like:

let
    Source = #"Folder Parameter",
    FilterRows = Table.SelectRows(Source, each not Text.Contains([Name], "$"))[[Name]],
    InsertRow= Table.InsertRows(FilterRows, 0, {[Name = "PAR"]})
in
    InsertRow

And

let
    Source = #"Folder Parameter",
    FilterRows = Table.SelectRows(Source, each not Text.Contains([Name], "$"))[[Name]],
    CombineTables = Table.Combine({FilterRows, Table.FromRecords({[Name = "PAR"]})})
in
    CombineTables

I hope this is helpful

1 Like
3

Hi @AllisterB, we’ve noticed that no response has been received from you since the 23rd of October. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

4

Thanks melissa - this looks like what i need.

Can you clarify - are the two last blocks of code for my sample code, two separate Queries or one query - can you explain.

Thanks

Allister

5

Two separate queries that do the same thing add a record with the Value “PAR”.
So you can pick the one you like best :wink:

1 Like