Forum Discussion
Combining a list containing columns with a list containing rows
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 |
Hi Anonymous ,
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.
2 Replies
Hi Anonymous ,
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.
Hi Anonymous - 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 )