Forum Discussion
How we can use row as a column heading from data files
Hi How we can use row as a column heading from data files?
in my data files every file has same column headers and combined in one table, here i wish to consider one column row for all table and filter or remove the duplicate rows without loosing other dated rows which has file per day and it's data on below snap i have promoted column headings and shows remaining duplicate rows. also please have a look the attached snaps where i wish to have help on this. refer image(capture1) and wann to be data column headings like Image (capture3)
4 Replies
v-piga-msftResident Rockstar
Hi Anonymous ,
Based your description, I think that we may achive your desired output in Query Editor.
I still have a little confused about the value for Persno column in Capture3, please describe it in details if it is convenient.
If it is convenient, could you share your data sample which could reproduce your scenario as table format so that I could have a test on it.
Best Regards,
Cherry
Ok
In below table i have pasted table from my sheet data Table 2.
Where you will find the rows with File name with Date in MM-DD-YYYY format. which displays after combining files in query. So now i wish to be have column heading like File Source name with Date | File date | Product Id which shows in Snap in my base post. and column has like below snap and automatically remove other duplicate rows need to be filter/remove from the table. Means like below table 1.
Please help
Table 1
Active 01-31-2019.xlsx File Date Product ID Active 04-07-2019.xlsx 01/27/19 523 Table 2
Active 01-06-2019.xlsx Note Excludes Active 01-13-2019.xlsx Note Excludes Active 01-20-2019.xlsx Note Excludes Active 01-27-2019.xlsx Note Excludes Active 01-31-2019.xlsx Note Excludes Active 01-06-2019.xlsx Group A Active 01-13-2019.xlsx Group A Active 01-20-2019.xlsx Group A Active 01-27-2019.xlsx Group A Active 01-31-2019.xlsx Group A Active 01-06-2019.xlsx File Date Product ID Active 01-13-2019.xlsx File Date Product ID Active 01-20-2019.xlsx File Date Product ID Active 01-27-2019.xlsx File Date Product ID Active 01-31-2019.xlsx File Date Product ID Active 04-07-2019.xlsx 04/07/19 - ACTIVE HEADCOUNT DETAILS as per SAP-HR Active 04-07-2019.xlsx 04/07/19 523 Active 04-07-2019.xlsx 04/07/19 7112 Active 04-07-2019.xlsx 04/07/19 7153 Active 04-07-2019.xlsx 04/07/19 7336 Active 04-07-2019.xlsx 04/07/19 7492 - v-piga-msft
Hi Anonymous ,
Thanks for your data sample and your desired output.
By my research of your data, I still confused about your logic to get the output Table 1 from the data sample Table2.
If it is convenient, could you explain the logic of it so that I could have a test on it.
Best Regards,
Cherry