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.
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