How we can use row as a column heading from data files

[Amardeep100115]

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)

 

[v-piga-msft]

Hi @Amardeep100115 ,

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

[Amardeep100115]

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 @Amardeep100115 ,

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

[Amardeep100115]

Thanks for writing back Every wee we download the report, it means for every month we have 4 or 5 file of that report and i have to consider last report of the month/last week of the month report. every report has same column headings and after combing files in power Query it shows rows with same heading accordingly file saved in source location. i wish to be select automatically file which is saved in last week of the every month. and skip other header rows in query editor. I have 3 -5 date sources where we are storing monthly 4-5 file every month. hope you got my point.