Forum Discussion
troubleshooting the issue in Excel file to create invoke function column
"I retrieve data from an Excel file containing three sheets. I attempted to create a template and a function, and then invoked that function in a column. However, it shows an error in the other sheets except for the one named 'Apr 2022.' How can I resolve this error?"
Would you like some help troubleshooting the issue in your Excel file?
Excel.Workbook function requires a binary value as first argument, since you passed 'Worksheet' - a parameter with value 'April 2022' as a value it is throwing an error.
Considering you want to use Excel.Workbook function in a custom column expression
I attempted to create a template and a function, and then invoked that function in a column.1. First copy the path of the folder having all your excel files.
2. The use the below code
let Source = Folder.Contents(<FolderPath>), TranformExcelFiles = Table.AddColumn(Source, "Custom", each Excel.Workbook(_, true) ) in TranformExcelFiles3. Incase if you want to create a function which can take the folder path as an input then converts them into table format then
(FolderPath as text)=> let Source = Folder.Contents(FolderPath), TranformExcelFiles = Table.AddColumn(Source, "Custom", each Excel.Workbook(_, true) ) in TranformExcelFiles4. If you want to create a template file then create a parameter for the folder path and use the above code.
Checkout this video if you have any questions
https://www.youtube.com/watch?v=ktgdDPNXiMg
Need a Power BI Consultation? Hire me on Upwork
Connect on LinkedIn
Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos.
Proud to be a Super User!
1 Reply
- tharunkumarRTK
Super User
Excel.Workbook function requires a binary value as first argument, since you passed 'Worksheet' - a parameter with value 'April 2022' as a value it is throwing an error.
Considering you want to use Excel.Workbook function in a custom column expression
I attempted to create a template and a function, and then invoked that function in a column.1. First copy the path of the folder having all your excel files.
2. The use the below code
let Source = Folder.Contents(<FolderPath>), TranformExcelFiles = Table.AddColumn(Source, "Custom", each Excel.Workbook(_, true) ) in TranformExcelFiles3. Incase if you want to create a function which can take the folder path as an input then converts them into table format then
(FolderPath as text)=> let Source = Folder.Contents(FolderPath), TranformExcelFiles = Table.AddColumn(Source, "Custom", each Excel.Workbook(_, true) ) in TranformExcelFiles4. If you want to create a template file then create a parameter for the folder path and use the above code.
Checkout this video if you have any questions
https://www.youtube.com/watch?v=ktgdDPNXiMg
Need a Power BI Consultation? Hire me on Upwork
Connect on LinkedIn
Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos.
Proud to be a Super User!