Forum Discussion
Append columns from the same datasource
HI I have a datasheet with ID's, description and date as columns and I need to append them so its all ine one place.
Datastructure Current:
9/17/200 9/24/200 10/04/2020
ID Description ID Description ID Description
AD1 Text 1 AD5 Text 5 AD3 Text 3
AF1 Text 5 AM5 Text 12 AN7 Text 9
I want it to be in the following format:
Date ID Description
9/17/2020 AD1 Text1
9/17/2020 AF1 Text 5
9/24/200 AD5 Text 5
9/24/200 AM5 Text 12
10/04/2020 AD3 Text 3
10/04/2020 AN7 Text 9
If someone has an idea how to implement this , help will be greatly appreciated.
Thanks
Hi Anonymous ,
Go to query editor >transform>"Use header as first row":
And you will see:
Then using "transposed Table">"Filled down">"Unpivot column 3 and column 4",you will see:
Adding an index column then back to data view;
Create a Rank column as below:
Rank = RANKX(FILTER('Table','Table'[Column2]=EARLIER('Table'[Column2])),'Table'[Index],,ASC,Dense)Finally in a matrix, you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
5 Replies
Hi Anonymous ,
Go to query editor >transform>"Use header as first row":
And you will see:
Then using "transposed Table">"Filled down">"Unpivot column 3 and column 4",you will see:
Adding an index column then back to data view;
Create a Rank column as below:
Rank = RANKX(FILTER('Table','Table'[Column2]=EARLIER('Table'[Column2])),'Table'[Index],,ASC,Dense)Finally in a matrix, you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
Thanks for your reply Kelly. However if there was a scenerio where the column headers from the source has a combination of text and dates then how would we handle that, for example , if the data looks something like below:
Late Items That need to be caught up Desc 9/27/2020 items = 6 Desc 10/4/2020 items = 14 Desc 10/11/2020 items = 0 Desc 10/18/2020 items = 18 Desc 10/25/2020 items = 1 Desc 11/1/2020 items = 16 Desc Item 1 gdkgkd Item 3 gethet Item 5 dfgbd Item 7 vgd Item 9 gth Item 11 kyu Item 13 i6jt Item 2 vsfvfsv Item 4 asfdsfs Item 6 mjgg Item 8 edw Item 10 jtyuj Item 12 fwef Item 14 sfef
Hi,
See if my solution here helps - Rearrange a multi heading dataset into a single heading one which is Pivot ready.
