Forum Discussion
Helper IIData copy and transformation challenge
Hi,
I have an interesting situation where I need to amalgamate data in several tables into one table and has been pulled into PBI from JIRA. One of the tables contains about 10 rows of information which comprise 1 record, they are grouped by a record ID which is the key from the other table I am working with. Each row of the record has the record ID, BUT, several records have a NULL record ID.
The items in the column circled in red I need to transform into column headers and the items in the column circled in green is the data that I need in each corresponding column. I am trying to do this in the most efficient way possible but not sure how to approach it.
ok. Try this.
Here is what the source looks like (if you have any blank rows, remove them). Add an index column starting at 1
Select the index column, go to to transform and add column, select the Standard option -> Modulo and type in the number of rows for each Key (in your screenshot there are 9). Add a new conditional column to return the value Item value where Modulo =1. Then do a fill down and rename the column:
Remove the index and Modulo columns.
Select the Item column and under Transform, select Pivot column. For the Values value, select the Record column and select "Don't aggregate" in the advanced options.
Finally remove the KeyC column to get
Sample PBIX file attached
8 Replies
PaulDBrownCommunity Champion
ok. Try this.
Here is what the source looks like (if you have any blank rows, remove them). Add an index column starting at 1
Select the index column, go to to transform and add column, select the Standard option -> Modulo and type in the number of rows for each Key (in your screenshot there are 9). Add a new conditional column to return the value Item value where Modulo =1. Then do a fill down and rename the column:
Remove the index and Modulo columns.
Select the Item column and under Transform, select Pivot column. For the Values value, select the Record column and select "Don't aggregate" in the advanced options.
Finally remove the KeyC column to get
Sample PBIX file attached
BarryMOK that did seem to work for me, there is 1 residual issue though. The record item are in the columns I need them to be BUT they are spread accross 9 separate rows. Here is a screen shot
What is the best way to aggregate the 9 rows of the record into 1 single row?
- PaulDBrown
Are all the items like that? Any chance you can share half a dozen records as they appear at the source?
- PaulDBrown
Use the "Transpose" function in Power Query, followed by "use first row as headers"
- BarryM
I have tried that but as the table has multiple records and columns I end up with a large number of expansions for each column and the table grows from 30 records to thousands of records with a lot of null cells and some errors.
This I'm suspecting with need to be scripted but not sure which DAX funtions to use or how I should write it.
- BarryM
Would that be Transpoe instaed of Pivot?
- PaulDBrown
No. The method I used in the long post was pivotting the field