Adding columns from a list with same data whole way down

[KK123456]

Hi, 

I'm relatively new to power query so need some help. 

I have a list with just one column and different data in each row and I have a table of data I'm working on. I want to add new columns to my table which takes the info in my list and pastes it the entire way down. The list i have has 60+ lines so trying to find a quicker way than add custom column for each one. 

Example: 

List
A
B
C
D
Table
Name	info	A (added column)	B (added column)	C (added Column)	D (added column)
Andy	1	A	B	C	D
Angela	2	A	B	C	D
Kevin	3	A	B	C	D
Jim	4	A	B	C	D

Thanks! 

 

[v-echaithra]

Hi @KK123456 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithra E.

[v-echaithra]

Hi @KK123456 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @KK123456 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithra E.

[dufoq3]

Hi @KK123456, another solution:

Output

let
    List = Text.ToList("ABCD"),
    Table = Table.FromColumns({{"Andy","Angela","Kevin","Jim"}, {1,2,3,4}}, type table[Name=text, info=number]),
    AddedListToColumns = Table.FromRows(List.TransformMany(Table.ToRows(Table),
        each {List},
        (x,y)=> x & y), Table.ColumnNames(Table) & List.Transform({1..List.Count(List)}, each "Col" & Text.From(_)))
in
    AddedListToColumns

[wdx223_Daniel]

NewStep=Table.FillDown(Table.FromColumns(Table.ToColumns(YourTable)&List.Zip(YourList),Table.ColumnNames(YourTable)&YourList),YourList)

[SundarRaj]

Hi @KK123456 , here's a solution. In this case, I belive List.Accumulate would work brilliantly and automate the entire process. Thanks!

Here's the code:

= List.Accumulate(Cols,#"Changed Type",(s,c) => Table.AddColumn(s,c, each c))

[PwerQueryKees]

@OwenAuger beat me to it, but here is my solution:

let
    #"Table to add columns to" = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],  // The original table
    #"List of ColumnNames" = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][List],  // Your list
    #"Converted to Table" = Table.FromList(#"List of ColumnNames", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Duplicated Column" = Table.DuplicateColumn(#"Converted to Table", "Column1", "Column1 - Copy"),
    #"Transposed Table" = Table.Transpose(#"Duplicated Column"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Table to add columns to", "New Columns", each #"Promoted Headers"),
    #"Expanded New Columns" = Table.ExpandTableColumn(#"Added Custom", "New Columns", #"List of ColumnNames")
in
    #"Expanded New Columns"

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[p45cal]

What form does the list take? I've assumed it starts life as a table.

See linked to workbook below.

It's probably not the slickest way!

First I took your list table, transposed it, appended it to itself (to create a duplicated row), promoted the first row as headers and left it at that.

Took your Name/Info table, added a custom column to bring in the list table, expanded that with a manual tweak o use the headers of the list table.

The workbook: https://app.box.com/s/p0jom6o7dmtgm100ymdyxwz13tngv32w

[OwenAuger · Accepted solution]

Hi @KK123456 

That's a slightly unusual requirement : )

If you have existing queries of this form:

List

(single-column table)

TableSource

then you could create a query like this to add columns with names & values both taken from List:

let
  ValuesList = List[List],
  #"Add List as Record" = Table.AddColumn(TableSource, "List", each Record.FromList(ValuesList, ValuesList)),
  #"Expand List to Columns" = Table.ExpandRecordColumn(#"Add List as Record", "List", ValuesList),
  #"Convert New Columns to Text" = Table.TransformColumns(
    #"Expand List to Columns",
    List.Transform(ValuesList, each {_, Text.From, type text})
  )
in
  #"Convert New Columns to Text"

The main idea is to convert the list to a record where each field's name & value is an item from the list with Record.FromList, then add/expand this in a column.

Alternatively, you could construct a table rather than a record to produce the same result:

let
  ValuesList = List[List],
  #"Add List as Table" = Table.AddColumn(TableSource, "List", each #table(ValuesList, {ValuesList})),
  #"Expand to Columns" = Table.ExpandTableColumn(#"Add List as Table", "List", ValuesList),
  #"Convert New Columns to Text" = Table.TransformColumns(
    #"Expand to Columns",
    List.Transform(ValuesList, each {_, Text.From, type text})
  )
in
  #"Convert New Columns to Text"

Small PBIX attached to illustrate.

Can you adapt this to your model?