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.
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
1. You could first refer to the following docs to learn about the expressions and functions I used in that statement.
Table.TransformColumnNames intends to transform each column name in the query one by one. Before transforming, the variable "vPosition" will try to find if a name exists in the #"Current Field Names" list. If it exists, it will return its offset in the list. If it doesn't exist, it will return -1 by default. Then the "if" conditional statement will return different values according to the vPosition's result. When vPosition is larger than or equal to 0, this means the original column names exists in #"Current Field Names" list, so it returns the corresponding position's name from #"Required Field Names" list. When vPosition is smaller than 0, this means the original column name doesn't exist, so it returns its name as it is in case of any error.
2. For the second point, I'm sorry I don't have any solution. We can only know the no. of columns after removing top 3 rows after the retrieval of data from CSV file. However the columns argument needs to be provided when the file is parsed as CSV file, so I have no idea how to get the no. of columns dynamically before Power Query parses the file as CSV.
Best Regards,
Jing
Dear Jing,
Thank you very much for your excellent explanation for point no. 1. Since there is no option known for point no. 2, I wish to conclude this query to be marked as resolved as rest of my requirements are complete as per your nice solution. Thanks a lot for helping me.
At this point, i have one last request if you can guide me for below mentioned point :
Why do we need to use a "let" and "in" as used in your solution above in last step. Can't it accept it by specifying "each" followed by rest of the statement / function.
With best regards
A "let" and "in" is not a must. You can use below code, which works the same. I used "let" and "int" because it can store the result in a variable and the variable can be used in the rest of the statement directly. This can avoid reevaluating it several times. This is my habit.
= Table.TransformColumnNames(#"Removed Other Columns", each if List.PositionOf(#"Current Field Names", _) >= 0 then #"Required Field Names"{List.PositionOf(#"Current Field Names", _)} else _)To mark the thread as resolved, you can click the "Accept as Solution" button on my first reply. Thank you!
Best regards,
Jing
Dear Jing
Thanks a lot for your guidance.
With best regards