Combining a list containing columns with a list containing rows

[PhilippDF]

Hi everyone!

I have two lists I get via a web request through Json.Document(Web.Contents(...). One contains information about the table columns. The other contains the row values. I want to cobine those two lists to get a table.

• The tow lists after converting them into a table:

• Expanding the column list gets me something like this:

columns

Header1

Header2

Header3

Header4

• Expanding the rows list gets me something like this

rows

Value1OfHeader1

Value1OfHeader2

Value1OfHeader3

Value1OfHeader4

Value2OfHeader1

Value2OfHeader2

Value2OfHeader3

Value2OfHeader4

.....

Value[n]OfHeader4

I tried to transpose the table and than expand the column list first and than the rows list but that only leads to multipling the rows list by the number of headers.

Now I was thinking to add a custum column which adds a number to the rows. In my example that would be a repeating sequence from 1 to 4. Also the column headers would get an index. Here also from 1 to 4. With this numbering I could merge the two lists and than create a pivot table. But this seems to be to much for something like that. Maybe you have another idea. Thanks in advance 🙂

• Index for column list

Index	columns
1	Header1
2	Header2
3	Header3
4	Header5

• customID for rows list

customID	rows
1	Value1OfHeader1
2	Value1OfHeader2
3	Value1OfHeader3
4	Value1OfHeader4
1	Value2OfHeader1
2	Value2OfHeader2
3	Value2OfHeader3
4	Value2OfHeader4
.....	.....
4	Value[n]OfHeader4

[Anonymous · Accepted solution]

Hi @PhilippDF ,

Please take a look at my possible solution.

First, here's the sample data I reproduced.

1.Add as new query for two lists.

2.You can get these two lists. Then convert them to tables.

3.Then add an index in Value table directly.

4.In Value(2) table, first create an index column.

5. Divide Index column by 4. From Transform tab > (Number Column) Standard > Divide.

6. Round up Index column. Transform > (Number Column) Rounding > Round Up.

7.Group by the Index column, select "All rows".

8.Add a custom column to add a custom index column.

9.Remove the unneeded columns, expand the new column with tables.

10.Finall you can merge Value table with Value(2) table.

11.Expand the column, remove the index column, then pivot.

Since my sample data, such as "Value1OfHeader1" is text, the result will be wrong, and your data is of type number, which can get the desired result.

You can download my attachment for more details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Daryl-Lynch-Bzy]

Hi @PhilippDF - I would like to suggest the following:

•  From the Column heading Json you want to create List to rename the Row headings.  These list will need to contain text value.  At the moment Index will be an Integer.  I best to change this to text.  Adding a pre-fix like "Column" is optional.  To get each use the following M expression to create 

RenameList = Table.ToRows( #"Previous Step") 

•  For the Rows table, add the pre-fix to the Index column if you want.  You need to pivot the custom column to create a table with four headings (i.e. "Column1", "Column2", "Column4", "Column4" ).  The key is to change the aggregation to "Don't Aggregate"

• Finally you need to use the Two lists to rename the Column1 to Heading1.  Add you lists like so.

#"Renamed Columns" = Table.RenameColumns( #"Pivot Step", RenameList )