Forum Discussion
Dynamic Columns Specification in CSV.Document
Dear All
Kindly help me with a workaround in power query for below mentioned situation.
- I have to combine two reports in CSV. Both of the CSVs have top three rows which I need to remove first.
File1 contains below mentioned fields: (Hypothetical field names)
Date,Sales,Product,Type,No_of_inst,EMI,Last_EMI_Dt,Cust_Type_Code,Customer_Type
File2 contains below mentioned fields:
Date,Sales,Product,Type,DP,Stock_St_Dt,Cust_Type_Code,Cust_Type,
2. Both of the CSVs have got different no. of columns. File1 – 9 Columns, File2 – 8 Columns
3. The no. of columns always reduce or increase every time new reports are provided to me.
4. Out of all the columns in these CSVs , I require only specific columns. I have prepared a list of required in fields in excel. The list of required fields can increase or reduce in future. At present required fields (Hypothetical) are as under:
Date,Sales,Product,Type,No_of_inst,Customer_Type (From File1)
Date,Sales,Product,Type,DP,Cust_Type (From File2)
Some of the fields are required from File1 and some from File2.
Combined list of required fields is as under:
Date,Sales,Product,Type,No_of_inst,Customer_Type (Cust_Type), DP
5. Some of the columns have same names in both of the CSVs. But some columns have same kind of data but different names in CSVs.
Date,Sales,Product,Type (File1)
Date,Sales,Product,Type (File2)
Above fields have same names in both of the CSVs.
Customer_Type (From File1) and Cust_Type (From File2)
Above field is having different field names but they are having same data. So the data in these columns should appear in only 1 column in the output.
6. I cannot mention specific no. of columns while using CSV.Document's "Columns" argument, because no. of columns can increase or reduce next time. I cannot skip to provide “Columns” argument. If I do so, the CSV.Document retrieves only two columns based on data in First 3 rows which are actually redundant and have to be removed as mentioned in point no. 1 above.
7. I cannot provide a list for "Columns" argument in CSV.Document because I have to remove top 3 rows before specifying columns. When I tried to provide a list in "Columns" argument in CSV.Document, it named the columns wrongly because the sequence of field names in my list differs with sequence of field names in CSV files and the sequence of field names can again change in future CSVs.
8. The field names in the CSVs are not proper for final output. Hence, I have to rename some / all of the required field names as under:
Current_Field_Name | Required_Field_Name |
Date | Report_Date |
Sales | Sales_Amount |
Product | Product_Description |
Type | Product_Type |
No_of_inst | No_of_EMIs |
Customer_Type(Cust_Type) | Constitution |
DP | Allowed_Amount |
I will maintain above list in excel so that I can dynamically update field names. I intend to use Table.ToRows or List.Zip but I am not sure how to use it as I am facing constraints as mentioned in foregoing points.
I request you all to help me with a solution which can take care all of the above requirements.
Regards
I create a sample according to your description. You may download my sample file to see the solution. The files I used are in the zip file.
Please notice: in "Field Names" query, you need to have multiple rows for columns that have same kind of data but different names in CSVs.
In "RequiredFields of Report1(Report2)", it should have all required field names in a single column.
For each report data query, we can provide a large enough number (e.g. 20 in my sample) for the "Columns" parameter in Csv.Document. This number should be larger than the possible maximum no. of columns in the future. Then remove top 3 rows, promote the first row as headers, select only required columns and rename these columns. Then append two queries into a single query to combine the data.
Change column data types in the combined query after you get all data for futher modeling/calculation.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
8 Replies
think it's better to provide some sample to test, and make you easily to deploy the code to your real data.
Dear wdx223_daniel
Thanks for taking interest. I am providing herewith some sample data. Hope it helps. My actual data is company provided hence, I cannot share here.
Report 1
ABC CO. LTD, CENTRAL BRANCH
REPORT AS ON 01.02.2023Date,Sales,Product,Type,No_of_inst,EMI,Last_EMI_Dt,Cust_Type_Code,Customer_Type
20.Feb.25,2269.96,Product1,Short_Term,119,6145,31.Jan.35,1,Individual
06.Feb.24,2090.39,Product2,Short_Term,110,7023,30.Apr.33,4,Company
09.Apr.24,58.07,Product3,Short_Term,107,6099,31.Mar.33,4,Company
10.Jul.24,720.39,Product4,Short_Term,109,5539,31.Aug.33,2,Proprietorship
01.Sep.24,1283.18,Product5,Short_Term,113,9856,28.Feb.34,3,Partnership
16.Aug.25,2149.1,Product3,Short_Term,119,7637,31.Jul.35,4,Company
29.Oct.24,2147.55,Product1,Short_Term,110,9566,31.Dec.33,1,Individual
19.Mar.24,736.56,Product5,Short_Term,111,5259,30.Jun.33,4,Company
29.Nov.25,894.57,Product1,Short_Term,117,6581,31.Aug.35,4,Company
06.Jun.24,682.96,Product3,Short_Term,109,6413,31.Jul.33,3,Partnership
19.Oct.24,2025.6,Product1,Short_Term,115,8197,31.May.34,1,Individual
06.Aug.25,1521.47,Product1,Short_Term,111,9744,30.Nov.34,3,Partnership
19.Jan.25,1425.18,Product4,Short_Term,106,8167,30.Nov.33,3,Partnership
07.Jan.24,215.61,Product4,Short_Term,113,8674,30.Jun.33,2,Proprietorship
15.May.24,2206.33,Product5,Short_Term,109,9000,30.Jun.33,4,CompanyReport2
ABC CO. LTD, CENTRAL BRANCH
REPORT AS ON 01.02.2023Date,Sales,Product,Type,DP,Stock_St_Dt,Cust_Type_Code,Cust_Type
24.Feb.25,41.05,Product3,Long_Term,12,16.Mar.25,1,Individual
12.May.25,1929.81,Product1,Long_Term,23,01.Jun.25,4,Company
24.May.24,2475.21,Product3,Long_Term,34,13.Jun.24,1,Individual
26.Feb.24,1472.55,Product1,Long_Term,45,17.Mar.24,4,Company
11.Aug.25,1196.43,Product1,Long_Term,56,31.Aug.25,1,Individual
01.Aug.25,2462.81,Product1,Long_Term,67,21.Aug.25,4,Company
05.Oct.25,689,Product3,Long_Term,78,25.Oct.25,2,Proprietorship
25.Nov.25,896.54,Product1,Long_Term,89,15.Dec.25,3,Partnership
06.Nov.25,1975.55,Product5,Long_Term,100,26.Nov.25,2,Proprietorship
18.May.24,562.39,Product5,Long_Term,111,07.Jun.24,1,Individual
02.Nov.24,1271.88,Product1,Long_Term,122,22.Nov.24,3,Partnership
03.Mar.24,1673.21,Product5,Long_Term,133,23.Mar.24,3,Partnership
26.Feb.24,190.09,Product3,Long_Term,144,17.Mar.24,3,Partnership
01.Jun.25,1780.44,Product4,Long_Term,155,21.Jun.25,3,Partnership
08.Mar.24,1840.18,Product4,Long_Term,166,28.Mar.24,1,IndividualI create a sample according to your description. You may download my sample file to see the solution. The files I used are in the zip file.
Please notice: in "Field Names" query, you need to have multiple rows for columns that have same kind of data but different names in CSVs.
In "RequiredFields of Report1(Report2)", it should have all required field names in a single column.
For each report data query, we can provide a large enough number (e.g. 20 in my sample) for the "Columns" parameter in Csv.Document. This number should be larger than the possible maximum no. of columns in the future. Then remove top 3 rows, promote the first row as headers, select only required columns and rename these columns. Then append two queries into a single query to combine the data.
Change column data types in the combined query after you get all data for futher modeling/calculation.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
