Forum Discussion
transform excel files into power query tables
I have multiple excel files that are to be transformed into a table, to be consumed by the pbi report.
Each of the excel worksheets are setup like a input form. It's content arrives in PowerQuery spread over 10 columns and 30+ rows, per form.
Sometimes the field names are above the values (ie: Contractor), and sometimes to the left of the values (ie:Project Title).
I have a list of field names that I can use to target the fields in this query.
Is there a way to read from the FieldList, and go search for that content within the above query.
I currently do this over many transformation queries, and was wondering if there was an easier way.
In my last transformation query, i have to pivot the content to arrive at the 45 columns per excelform, and running into errors.
Are there custom M functions that would help?
1 Reply
You can use the List.Contains function to look at one list and see if it contains the values from another list. You can convert a column in a table to a list by simply referencing the table and column like this:
#"Query Name or Step"[Field Name]
I have a blog post on List.Contains here that may provide some guidance. ehansalytics
Note that performance can suffer if you are itterating over a table and doing that for each row, but to help more I'd need to see some info.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
