headers not aligned in new REST API dataset

[Anonymous]

using REST API, I got a dataset like these:

code
price
date
	a1
	33,99
	11/11/2016
	b2
	34,88
	12/12/2016
	cc3c
	35,77
	09/09/2016
	...

it continues going down to more than a million rows.

how can i turn this dataset into something like the dataset below? query editor or DAX?

code	price	date
a1	33,99	11/11/2016
b2	34,88	12/12/2016
ccc3c	35,77	09/09/2016
...

this is only an example. i got a total of 21 headers.

[dkay84_PowerBI]

I think I have this solved except for the final part, and hopefully someone here can finalize it.

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.

[dkay84_PowerBI]

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
    Source

Now, 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.

[marco_lima]

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!

[dkay84_PowerBI · Accepted solution]

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).

[marco_lima]

Thanks a lot! I apreciate your rapid response.

[Anonymous]

it worked! thanks a lot for your support! your ninja code and correct steps sequence are awesome!

to append the queries, the column names must be the same or they will skip columns, but I figured it out.
the blank query needs to start with equal sign "=" or it will treat as text, but it is ok now.

thanks!

[dkay84_PowerBI]

Glad it worked!  Just remember, if you hard coded the number of rows to keep/drop based on the number of fields, and that number changes, you will need to alter the steps.  There is a way to make it dynamic so it autodetects (there is obviously "Remove Blanks" but sometimes those empty cells aren't truly blank or null, but a "" string, and thus Remove Blanks or Empty Rows won't work automatically).  Let me know if you need that solution.

[Anonymous]

please, how to make it dynamic so it autodetects column/row quantity changes?

[dkay84_PowerBI]

For dynamic filtering, imagine this example.  You have a report/excel/csv file where the query details are provided at the top and these details could be more or less rows and you cant hard code in the number of rows to truncate).  Below the query details are some blank rows and then the actual data, complete with the column headers.  In this case, you need a way to dynamically calculate the number of rows to remove from the top (although this can be adapted to keep rows or remove/keep from bottom).

In general, the way to dynamically filter is to:

1. Add an index column
2. Replace blank values in column (in my example above, you could replace blanks/nulls with some unique identifier, i.e. * or |).  If your column headers are static, you could also filter by that value, but this will break if the column headers ever change.  The idea is to have some unique value to filter that will give you the row number (index value) of the where the data starts
3. Add a Keep Top Rows (or variant) step. Enter the number 1 for rows to keep
4. Go to the Advanced editor and edit the Keep Top Rows step:
   1. Change the reference step from the previous step (#"Previous Step Name") to an earlier step name (i.e. #"Added Index").  This will allow you to work with the full data set instead of the filtered dataset.
   2. Change the hard coded value for rows to keep (1) to a reference to the Index column and row 1
      1. Syntax = TableName[Index]{0}
      2. Note:  The query editor refers to row 1 as 0, row 2 as 1 etc.
      3. This essentially tells the Remove Top Rows step to remove the number of rows that is the value in the Index column.  Remember that after you filtered the table earlier, that value in the Index column represents the first row where the data starts.  Therefore that value is equal to the number of rows you want to remove (depending on your table's structure, you may need to add additional rows --> TableName[Index]{0}+1

In the image below, imagine that you want to extract the data starting with ID, Number, etc. but the number of rows above (both blank and text) is variable.  Therefore, you cannot just remove blanks and then remove the top 2 rows. By adding an index and then filtering column 1 with a Text = "ID" filter, the Index column will then give you the row number that the data starts on (and in this example, ends on).  This can allow you to dynamically filter using the steps above.

A full description can be found in "M is for Data Monkey" Chapter 20

[dkay84_PowerBI]

It sounds like your example of replacing values and then filtering will work dynamically.

The other solution is more complicated and requires additional steps and custom code, and still relies on a filter step so performance will not be better than your solution.

[Anonymous]

yes, this is exactly what happened, but i fixed it by myself.
I got null values and "" values. so I substitute values and all "" becomes 0, while null stays as null and then I can remove blank rows.

using my example at the first post, I got null only at the 3 first rows. below that, all other blanks are "" so I changed to 0 (zero) and everything now runs smootly nice.

once again, thanks a lot! your logic pathway and code is something I couldn´t figure it out by myself.

[marco_lima]

Sure it would be awesome to know how to do it. Can you show me that solution ?