I am having a column with series of elements an excel sheet called list with series of letters and an excel sheet called column to add to each letter of the list
Here is the sheet of the list which I have created
|---------------------|
| Name |
|---------------------|
| A |
|---------------------|
| B |
|---------------------|
| C |
|---------------------|
| D |
and here is the column to add files
|---------------------|-----------------| -------------|-----------------|
| Pro1 | Pro2 | Pro3 | Pro4 |
|---------------------|-----------------| -------------|-----------------|
| 56 | dgdt | gdro1 | gggggro1 |
|---------------------|-----------------| -------------|-----------------|
| 56 | dgdt | gdro1 | gggggro1 |
|---------------------|-----------------| -------------|-----------------|
| 4 | dgdt | gdro1 | gggggro1 |
|---------------------|-----------------| -------------|-----------------|
| 4 | 4 4 4
for each element i am trying add 4 columns from other files,The following would be result
I have tried with the following codes and they are not working
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{" Name ", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type1"),
#"repeat Table" = Table.Repeat(#"Transposed Table",4),
#"Added Index" = Table.AddIndexColumn(#"repeat Table", "Index", 1, 1)
in
#"Added Index"
let
Source = Table.NestedJoin(#"columns to add", {"Index"}, list, {"Index"}, "list", JoinKind.FullOuter),
#"Expanded list" = Table.ExpandTableColumn(Source, "list", {"Column1", "Column2", "Column3", "Column4", "Index"}, {"list.Column1", "list.Column2", "list.Column3", "list.Column4", "list.Index"})
in
#"Expanded list"
How can I do this?