Forum Discussion
headers not aligned in new REST API dataset
First, you need to reference your original table
In the query editor, where you see your tables/queries on the left hand side, right click on the original table and click "reference"
and keep only the first column, removing all blank rows (might need to hard code a number for Keep Top Rows) and transpose. DO NOT PROMOTE HEADERS
On this referenced table (auto named "xxx (2)") right click on the first column (the column with your eventual headers) and click "remove other columns"
Then, click on "Keep Rows" (Home tab at the top) and choose "Keep Top Rows". If you will always have the same number of headers, you can hard code this number in. If you need a dynamic solution let me know as there is a way to do that too.
Then, on the Transform tab, hit the "Transpose" button. IF the software auto promotes headers, then undo that step by clicking on the "x" next to the step on the right hand side of the query editor. Otherwise, you are done with this table for now.
Next, you will reference your original table again, and this time keep only the second column, removing all blank rows like the first step.Repeat the steps from earlier but for the second column, only this time you will "Remove Top Rows" using a hard coded number (again there is a dynamic solution if this number will change in the future).
Now, add an index column starting with 1.
Go to "Add Column" tab, and click on the drop down arrow next to the "Index Column" button and choose "From 1"
Then, transform the index column with a divide by 3 (this number should be the number of rows that make up a record, essentially the number of columns you need) and then round up. This will give you an index column that is like 1,1,1,2,2,2,3,3,3, etc. which should align with your data.
Highlight this index column by clicking on the header, then, on the Transform tab, click the "Standard" button (has the mathematical symbols on it). Choose "Divide" and use the number 3. Then, just to the right of the "Standard" button, click on the "Rounding" button and choose "Round Up".
Now you can group by the index column and choose "All Rows" for the aggregation. The end result is a table of tables, where each table is your data chunk.
On the Transform tab at the far left there is the "Group by" button. Click that and choose the Index column to group by and for the aggregation select "All Rows" (names here dont matter).
You need to create a custom function that transposes the table. This way you can add a Invoke Function column that, when expanded, should create 3 columns from the 3 records in each grouped table, and append them automatically.On the Home tab, click on "New Source" (New Query area in upper left) and choose "Blank Query". Open the Advanced Editor and copy/paste the code from my other post in replacing the existing code. Name this query fnTranspose.
Go back to the table/query that has been grouped and go to the Add Column tab. Click "Invoke Custom Function" and choose fnTranspose. Make sure that you change the little dropdown arrow to say "Table Column" and choose the column name that has the tables in it (from the grouping procedure earlier).
After adding this column, click on the little arrows in the top right of the new column to expand the tables, and it should automatically append these tables together after performing the operations in the function fnTranspose.
Finally you need to append your first referenced table with the second referenced table and then promote headers.Click on the first Reference table (the one with just one row with the names of the fields). Then, on the Home tab, upper right, click on the Append Queries button and choose "Append as New". For your second table, on the drop down list, choose the table that was the table of tables that used the fnTranspose function.
Once the tables are appended, you can click on the "Promote Headers" button to get the top row as headers.
Finally, for each of the intermediate tables/queries, right click and uncheck Enable Load (you wont need these tables in your data model and they will just take up memory).
First, you need to reference your original table and keep only the first column, removing all blank rows (might need to hard code a number for Keep Top Rows) and transpose. DO NOT PROMOTE HEADERS
Next, you will reference your original table again, and this time keep only the second column, removing all blank rows like the first step. Now, add an index column starting with 1. Then, transform the index column with a divide by 3 (this number should be the number of rows that make up a record, essentially the number of columns you need) and then round up. This will give you an index column that is like 1,1,1,2,2,2,3,3,3, etc. which should align with your data. Now you can group by the index column and choose "All Rows" for the aggregation. The end result is a table of tables, where each table is your data chunk.
The final step that I am not sure how to complete is that you need to create a custom function that transposes the table. This way you can add a Invoke Function column that, when expanded, should create 3 columns from the 3 records in each grouped table, and append them automatically.
Finally you need to append your first referenced table with the second referenced table and then promote headers.
Edit to above:
I think I got the function part. Create a blank query with the following code and name it whatever you want (fnTranspose):
let Source = (column as table) => let Source = column, #"Transposed Table" = Table.Transpose(Source),
#"Keep Row" = Table.FirstN(#"Transposed Table",1) in #"Keep Row" in SourceNow, after you get to the point where you have the table of tables, add a column using the Invoke Custom Funciton and use the fnTranspose function, but change the drop down to table column (instead of table) and choose the column which has the tables.
I am newbie in Power BI, so I am not able to reproduce thoses steps, could you show an example step-by-step ?
Thank you in advance!
First, you need to reference your original table
In the query editor, where you see your tables/queries on the left hand side, right click on the original table and click "reference"
and keep only the first column, removing all blank rows (might need to hard code a number for Keep Top Rows) and transpose. DO NOT PROMOTE HEADERS
On this referenced table (auto named "xxx (2)") right click on the first column (the column with your eventual headers) and click "remove other columns"
Then, click on "Keep Rows" (Home tab at the top) and choose "Keep Top Rows". If you will always have the same number of headers, you can hard code this number in. If you need a dynamic solution let me know as there is a way to do that too.
Then, on the Transform tab, hit the "Transpose" button. IF the software auto promotes headers, then undo that step by clicking on the "x" next to the step on the right hand side of the query editor. Otherwise, you are done with this table for now.
Next, you will reference your original table again, and this time keep only the second column, removing all blank rows like the first step.Repeat the steps from earlier but for the second column, only this time you will "Remove Top Rows" using a hard coded number (again there is a dynamic solution if this number will change in the future).
Now, add an index column starting with 1.
Go to "Add Column" tab, and click on the drop down arrow next to the "Index Column" button and choose "From 1"
Then, transform the index column with a divide by 3 (this number should be the number of rows that make up a record, essentially the number of columns you need) and then round up. This will give you an index column that is like 1,1,1,2,2,2,3,3,3, etc. which should align with your data.
Highlight this index column by clicking on the header, then, on the Transform tab, click the "Standard" button (has the mathematical symbols on it). Choose "Divide" and use the number 3. Then, just to the right of the "Standard" button, click on the "Rounding" button and choose "Round Up".
Now you can group by the index column and choose "All Rows" for the aggregation. The end result is a table of tables, where each table is your data chunk.
On the Transform tab at the far left there is the "Group by" button. Click that and choose the Index column to group by and for the aggregation select "All Rows" (names here dont matter).
You need to create a custom function that transposes the table. This way you can add a Invoke Function column that, when expanded, should create 3 columns from the 3 records in each grouped table, and append them automatically.On the Home tab, click on "New Source" (New Query area in upper left) and choose "Blank Query". Open the Advanced Editor and copy/paste the code from my other post in replacing the existing code. Name this query fnTranspose.
Go back to the table/query that has been grouped and go to the Add Column tab. Click "Invoke Custom Function" and choose fnTranspose. Make sure that you change the little dropdown arrow to say "Table Column" and choose the column name that has the tables in it (from the grouping procedure earlier).
After adding this column, click on the little arrows in the top right of the new column to expand the tables, and it should automatically append these tables together after performing the operations in the function fnTranspose.
Finally you need to append your first referenced table with the second referenced table and then promote headers.Click on the first Reference table (the one with just one row with the names of the fields). Then, on the Home tab, upper right, click on the Append Queries button and choose "Append as New". For your second table, on the drop down list, choose the table that was the table of tables that used the fnTranspose function.
Once the tables are appended, you can click on the "Promote Headers" button to get the top row as headers.
Finally, for each of the intermediate tables/queries, right click and uncheck Enable Load (you wont need these tables in your data model and they will just take up memory).
