topic Having trouble organising API json data in Developer

topic Having trouble organising API json data in Developer https://community.fabric.microsoft.com/t5/Developer/Having-trouble-organising-API-json-data/m-p/559637#M17513 <P>Hi, </P><P> </P><P>I'm using the following API to pull json data into Power BI:</P><P><A href="https://www.aha.io/api/resources/custom_pivot" target="_blank">https://www.aha.io/api/resources/custom_pivot</A></P><P> </P><P>I'm using the list view. </P><P> </P><P>The json data comes in in Pagination, Columns and Rows. </P><P> </P><P>Expanding coumns, I get 28 rows of data. These are the column headers in my list. </P><P> </P><P>Expanding rows, I get >1000 lines. Each repeating 28 rows of data, should be one row in the table (corresponding with the 28 column headers above). </P><P> </P><P>I'm wondering if anyone has any hints on how to combine the 2 data sources into one table? </P><P> </P><P>For example</P><P> </P><TABLE><TBODY><TR><TD>Table: columns</TD></TR><TR><TD> </TD><TD>Column1.title</TD></TR><TR><TD>1</TD><TD>Product Line (Product line)</TD></TR><TR><TD>2</TD><TD>Product name</TD></TR><TR><TD>3</TD><TD>Product ID</TD></TR><TR><TD>4</TD><TD>Release name</TD></TR><TR><TD>5</TD><TD>Release ID</TD></TR><TR><TD>6</TD><TD>Release created at</TD></TR><TR><TD>7</TD><TD>Release start date</TD></TR><TR><TD>8</TD><TD>Release date</TD></TR><TR><TD>9</TD><TD>Release owner</TD></TR><TR><TD>10</TD><TD>Release status</TD></TR><TR><TD>11</TD><TD>Feature name</TD></TR><TR><TD>12</TD><TD>Feature ID</TD></TR><TR><TD>13</TD><TD>Feature created at</TD></TR><TR><TD>14</TD><TD>Feature start date</TD></TR><TR><TD>15</TD><TD>Feature due date</TD></TR><TR><TD>16</TD><TD>Feature status</TD></TR><TR><TD>17</TD><TD>Feature URL</TD></TR><TR><TD>18</TD><TD>Requirement assigned to</TD></TR><TR><TD>19</TD><TD>Jira id</TD></TR><TR><TD>20</TD><TD>JIRA Sprint</TD></TR><TR><TD>21</TD><TD>Jira key</TD></TR><TR><TD>22</TD><TD>Jira url</TD></TR><TR><TD>23</TD><TD>Requirement name</TD></TR><TR><TD>24</TD><TD>Requirement ID</TD></TR><TR><TD>25</TD><TD>Requirement created at</TD></TR><TR><TD>26</TD><TD>Jira id</TD></TR><TR><TD>27</TD><TD>Jira key</TD></TR><TR><TD>28</TD><TD>Jira url</TD></TR></TBODY></TABLE><P> </P><P> </P><TABLE><TBODY><TR><TD>Table: rows</TD></TR><TR><TD> </TD><TD>Column1.plain_value</TD></TR><TR><TD>1</TD><TD>Product Line (Product line) 1</TD></TR><TR><TD>2</TD><TD>Product name 1</TD></TR><TR><TD>3</TD><TD>Product ID 1</TD></TR><TR><TD>4</TD><TD>Release name 1</TD></TR><TR><TD>5</TD><TD>Release ID 1</TD></TR><TR><TD>6</TD><TD>Release created at 1</TD></TR><TR><TD>7</TD><TD>Release start date 1</TD></TR><TR><TD>8</TD><TD>Release date 1</TD></TR><TR><TD>9</TD><TD>Release owner 1</TD></TR><TR><TD>10</TD><TD>Release status 1</TD></TR><TR><TD>11</TD><TD>Feature name 1</TD></TR><TR><TD>12</TD><TD>Feature ID 1</TD></TR><TR><TD>13</TD><TD>Feature created at 1</TD></TR><TR><TD>14</TD><TD>Feature start date 1</TD></TR><TR><TD>15</TD><TD>Feature due date 1</TD></TR><TR><TD>16</TD><TD>Feature status 1</TD></TR><TR><TD>17</TD><TD>Feature URL 1</TD></TR><TR><TD>18</TD><TD>Requirement assigned to 1</TD></TR><TR><TD>19</TD><TD>Jira id 1</TD></TR><TR><TD>20</TD><TD>JIRA Sprint 1</TD></TR><TR><TD>21</TD><TD>Jira key 1</TD></TR><TR><TD>22</TD><TD>Jira url 1</TD></TR><TR><TD>23</TD><TD>Requirement name 1</TD></TR><TR><TD>24</TD><TD>Requirement ID 1</TD></TR><TR><TD>25</TD><TD>Requirement created at 1</TD></TR><TR><TD>26</TD><TD>Jira id 1</TD></TR><TR><TD>27</TD><TD>Jira key 1</TD></TR><TR><TD>28</TD><TD>Jira url 1</TD></TR><TR><TD>1</TD><TD>Product Line (Product line) 2</TD></TR><TR><TD>2</TD><TD>Product name 2</TD></TR><TR><TD>3</TD><TD>Product ID 2</TD></TR><TR><TD>4</TD><TD>Release name 2</TD></TR><TR><TD>5</TD><TD>Release ID 2</TD></TR><TR><TD>6</TD><TD>Release created at 2</TD></TR><TR><TD>7</TD><TD>Release start date 2</TD></TR><TR><TD>8</TD><TD>Release date 2</TD></TR><TR><TD>9</TD><TD>Release owner 2</TD></TR><TR><TD>10</TD><TD>Release status 2</TD></TR><TR><TD>11</TD><TD>Feature name 2</TD></TR><TR><TD>12</TD><TD>Feature ID 2</TD></TR><TR><TD>13</TD><TD>Feature created at 2</TD></TR><TR><TD>14</TD><TD>Feature start date 2</TD></TR><TR><TD>15</TD><TD>Feature due date 2</TD></TR><TR><TD>16</TD><TD>Feature status 2</TD></TR><TR><TD>17</TD><TD>Feature URL 1</TD></TR><TR><TD>18</TD><TD>Requirement assigned to 2</TD></TR><TR><TD>19</TD><TD>Jira id 2</TD></TR><TR><TD>20</TD><TD>JIRA Sprint 2</TD></TR><TR><TD>21</TD><TD>Jira key 2</TD></TR><TR><TD>22</TD><TD>Jira url 2</TD></TR><TR><TD>23</TD><TD>Requirement name 2</TD></TR><TR><TD>24</TD><TD>Requirement ID 2</TD></TR><TR><TD>25</TD><TD>Requirement created at 2</TD></TR><TR><TD>26</TD><TD>Jira id 2</TD></TR><TR><TD>27</TD><TD>Jira key 2</TD></TR><TR><TD>28</TD><TD>Jira url 2</TD></TR><TR><TD>29</TD><TD>etc etc (Repeats in 28 row blocks)</TD></TR></TBODY></TABLE><P> </P><P>Desired output:</P><TABLE><TBODY><TR><TD>Product Line (Product line)</TD><TD>Product name</TD><TD>Product ID</TD><TD>Release name</TD><TD>Release ID</TD><TD>Release created at</TD><TD>Release start date</TD><TD>Release date</TD><TD>Release owner</TD><TD>Release status</TD><TD>etc</TD></TR><TR><TD>Product Line (Product line) 1</TD><TD>Product name 1</TD><TD>Product ID 1</TD><TD>Release name 1</TD><TD>Release ID 1</TD><TD>Release created at 1</TD><TD>Release start date 1</TD><TD>Release date 1</TD><TD>Release owner 1</TD><TD>Release status 1</TD><TD>etc</TD></TR><TR><TD>Product Line (Product line) 2</TD><TD>Product name 2</TD><TD>Product ID 2</TD><TD>Release name 2</TD><TD>Release ID 2</TD><TD>Release created at 2</TD><TD>Release start date 2</TD><TD>Release date 2</TD><TD>Release owner 2</TD><TD>Release status 2</TD><TD>etc</TD></TR></TBODY></TABLE><P> </P><P> </P><P>Thanks in advance for the help! </P> Sun, 04 Nov 2018 23:00:32 GMT lidderdj 2018-11-04T23:00:32Z Having trouble organising API json data https://community.fabric.microsoft.com/t5/Developer/Having-trouble-organising-API-json-data/m-p/559637#M17513 <P>Hi, </P><P> </P><P>I'm using the following API to pull json data into Power BI:</P><P><A href="https://www.aha.io/api/resources/custom_pivot" target="_blank">https://www.aha.io/api/resources/custom_pivot</A></P><P> </P><P>I'm using the list view. </P><P> </P><P>The json data comes in in Pagination, Columns and Rows. </P><P> </P><P>Expanding coumns, I get 28 rows of data. These are the column headers in my list. </P><P> </P><P>Expanding rows, I get >1000 lines. Each repeating 28 rows of data, should be one row in the table (corresponding with the 28 column headers above). </P><P> </P><P>I'm wondering if anyone has any hints on how to combine the 2 data sources into one table? </P><P> </P><P>For example</P><P> </P><TABLE><TBODY><TR><TD>Table: columns</TD></TR><TR><TD> </TD><TD>Column1.title</TD></TR><TR><TD>1</TD><TD>Product Line (Product line)</TD></TR><TR><TD>2</TD><TD>Product name</TD></TR><TR><TD>3</TD><TD>Product ID</TD></TR><TR><TD>4</TD><TD>Release name</TD></TR><TR><TD>5</TD><TD>Release ID</TD></TR><TR><TD>6</TD><TD>Release created at</TD></TR><TR><TD>7</TD><TD>Release start date</TD></TR><TR><TD>8</TD><TD>Release date</TD></TR><TR><TD>9</TD><TD>Release owner</TD></TR><TR><TD>10</TD><TD>Release status</TD></TR><TR><TD>11</TD><TD>Feature name</TD></TR><TR><TD>12</TD><TD>Feature ID</TD></TR><TR><TD>13</TD><TD>Feature created at</TD></TR><TR><TD>14</TD><TD>Feature start date</TD></TR><TR><TD>15</TD><TD>Feature due date</TD></TR><TR><TD>16</TD><TD>Feature status</TD></TR><TR><TD>17</TD><TD>Feature URL</TD></TR><TR><TD>18</TD><TD>Requirement assigned to</TD></TR><TR><TD>19</TD><TD>Jira id</TD></TR><TR><TD>20</TD><TD>JIRA Sprint</TD></TR><TR><TD>21</TD><TD>Jira key</TD></TR><TR><TD>22</TD><TD>Jira url</TD></TR><TR><TD>23</TD><TD>Requirement name</TD></TR><TR><TD>24</TD><TD>Requirement ID</TD></TR><TR><TD>25</TD><TD>Requirement created at</TD></TR><TR><TD>26</TD><TD>Jira id</TD></TR><TR><TD>27</TD><TD>Jira key</TD></TR><TR><TD>28</TD><TD>Jira url</TD></TR></TBODY></TABLE><P> </P><P> </P><TABLE><TBODY><TR><TD>Table: rows</TD></TR><TR><TD> </TD><TD>Column1.plain_value</TD></TR><TR><TD>1</TD><TD>Product Line (Product line) 1</TD></TR><TR><TD>2</TD><TD>Product name 1</TD></TR><TR><TD>3</TD><TD>Product ID 1</TD></TR><TR><TD>4</TD><TD>Release name 1</TD></TR><TR><TD>5</TD><TD>Release ID 1</TD></TR><TR><TD>6</TD><TD>Release created at 1</TD></TR><TR><TD>7</TD><TD>Release start date 1</TD></TR><TR><TD>8</TD><TD>Release date 1</TD></TR><TR><TD>9</TD><TD>Release owner 1</TD></TR><TR><TD>10</TD><TD>Release status 1</TD></TR><TR><TD>11</TD><TD>Feature name 1</TD></TR><TR><TD>12</TD><TD>Feature ID 1</TD></TR><TR><TD>13</TD><TD>Feature created at 1</TD></TR><TR><TD>14</TD><TD>Feature start date 1</TD></TR><TR><TD>15</TD><TD>Feature due date 1</TD></TR><TR><TD>16</TD><TD>Feature status 1</TD></TR><TR><TD>17</TD><TD>Feature URL 1</TD></TR><TR><TD>18</TD><TD>Requirement assigned to 1</TD></TR><TR><TD>19</TD><TD>Jira id 1</TD></TR><TR><TD>20</TD><TD>JIRA Sprint 1</TD></TR><TR><TD>21</TD><TD>Jira key 1</TD></TR><TR><TD>22</TD><TD>Jira url 1</TD></TR><TR><TD>23</TD><TD>Requirement name 1</TD></TR><TR><TD>24</TD><TD>Requirement ID 1</TD></TR><TR><TD>25</TD><TD>Requirement created at 1</TD></TR><TR><TD>26</TD><TD>Jira id 1</TD></TR><TR><TD>27</TD><TD>Jira key 1</TD></TR><TR><TD>28</TD><TD>Jira url 1</TD></TR><TR><TD>1</TD><TD>Product Line (Product line) 2</TD></TR><TR><TD>2</TD><TD>Product name 2</TD></TR><TR><TD>3</TD><TD>Product ID 2</TD></TR><TR><TD>4</TD><TD>Release name 2</TD></TR><TR><TD>5</TD><TD>Release ID 2</TD></TR><TR><TD>6</TD><TD>Release created at 2</TD></TR><TR><TD>7</TD><TD>Release start date 2</TD></TR><TR><TD>8</TD><TD>Release date 2</TD></TR><TR><TD>9</TD><TD>Release owner 2</TD></TR><TR><TD>10</TD><TD>Release status 2</TD></TR><TR><TD>11</TD><TD>Feature name 2</TD></TR><TR><TD>12</TD><TD>Feature ID 2</TD></TR><TR><TD>13</TD><TD>Feature created at 2</TD></TR><TR><TD>14</TD><TD>Feature start date 2</TD></TR><TR><TD>15</TD><TD>Feature due date 2</TD></TR><TR><TD>16</TD><TD>Feature status 2</TD></TR><TR><TD>17</TD><TD>Feature URL 1</TD></TR><TR><TD>18</TD><TD>Requirement assigned to 2</TD></TR><TR><TD>19</TD><TD>Jira id 2</TD></TR><TR><TD>20</TD><TD>JIRA Sprint 2</TD></TR><TR><TD>21</TD><TD>Jira key 2</TD></TR><TR><TD>22</TD><TD>Jira url 2</TD></TR><TR><TD>23</TD><TD>Requirement name 2</TD></TR><TR><TD>24</TD><TD>Requirement ID 2</TD></TR><TR><TD>25</TD><TD>Requirement created at 2</TD></TR><TR><TD>26</TD><TD>Jira id 2</TD></TR><TR><TD>27</TD><TD>Jira key 2</TD></TR><TR><TD>28</TD><TD>Jira url 2</TD></TR><TR><TD>29</TD><TD>etc etc (Repeats in 28 row blocks)</TD></TR></TBODY></TABLE><P> </P><P>Desired output:</P><TABLE><TBODY><TR><TD>Product Line (Product line)</TD><TD>Product name</TD><TD>Product ID</TD><TD>Release name</TD><TD>Release ID</TD><TD>Release created at</TD><TD>Release start date</TD><TD>Release date</TD><TD>Release owner</TD><TD>Release status</TD><TD>etc</TD></TR><TR><TD>Product Line (Product line) 1</TD><TD>Product name 1</TD><TD>Product ID 1</TD><TD>Release name 1</TD><TD>Release ID 1</TD><TD>Release created at 1</TD><TD>Release start date 1</TD><TD>Release date 1</TD><TD>Release owner 1</TD><TD>Release status 1</TD><TD>etc</TD></TR><TR><TD>Product Line (Product line) 2</TD><TD>Product name 2</TD><TD>Product ID 2</TD><TD>Release name 2</TD><TD>Release ID 2</TD><TD>Release created at 2</TD><TD>Release start date 2</TD><TD>Release date 2</TD><TD>Release owner 2</TD><TD>Release status 2</TD><TD>etc</TD></TR></TBODY></TABLE><P> </P><P> </P><P>Thanks in advance for the help! </P> Sun, 04 Nov 2018 23:00:32 GMT https://community.fabric.microsoft.com/t5/Developer/Having-trouble-organising-API-json-data/m-p/559637#M17513 lidderdj 2018-11-04T23:00:32Z