Forum Discussion
Dynamic Columns Specification in CSV.Document
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.
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.Dear v-jingzhang
Thanks a lot for taking interest. The solution is definitely useful and it is on the lines of my requirement. However, kindly help / advise on -
1. Last step on each of Report1 / Report2 quereis (Reproduced hereunder). Since, I am new to Power Query I am unable to understand this constructoin.
"= Table.TransformColumnNames(#"Removed Other Columns", each let vPosition = List.PositionOf(#"Current Field Names", _) in if vPosition >= 0 then #"Required Field Names"{vPosition} else _)"
This step is super cool. Please advise how it works.
2. The queries are providing me the solution which I need except one point. As advised by you, the columns argument in CSV.Document has to be provided as an input. It would be much helpful if there is a way to make it dynamic / automize the counting of all columns before retrieval of data from binary file CSV. Please advise if you can provide solution for that.
Regards
