Forum Discussion
Transform File pulling data from other tab
Hi,
I have a power bi file that is pulling data from thousands of excel files. I want the BI to pull a certain tab all with the same name in all the files. It works in most cases but there are a few files where is pulls in data from tabs with similar names. The tab I want to pull is "Final_Payment HW" the tab is is also pulling is "Payment HW". Below is the Transform file logic. Has anyone incountered this issue?
= (Parameter1) => let
Source = Excel.Workbook(Parameter1, null, true),
#"Final_H&W_Sheet" = Source{[Item="Final_Payment HW",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Final_H&W_Sheet", [PromoteAllScalars=true])
in
#"Promoted Headers"
Thanks!
Basing on just the M Script itself, your query shouldn't be pulling data other than from Final_Payment HW. In fact if you change the item name into something else and it doesn't exist, the query would throw an error.
Below is a screenshot of a typical Source step with an excel file as the data source and the typical M script to retrieve the contents of Table in Data column in the first row would be = Source{[Item="Sheet1",Kind="Sheet"]}[Data].
Now if I changed the item name to just Sheet instead, that would result to an error as Sheet doesn't exist iin column Item.
2 Replies
Basing on just the M Script itself, your query shouldn't be pulling data other than from Final_Payment HW. In fact if you change the item name into something else and it doesn't exist, the query would throw an error.
Below is a screenshot of a typical Source step with an excel file as the data source and the typical M script to retrieve the contents of Table in Data column in the first row would be = Source{[Item="Sheet1",Kind="Sheet"]}[Data].
Now if I changed the item name to just Sheet instead, that would result to an error as Sheet doesn't exist iin column Item.
Thats very strange. Can you show a picture of the excel tab in the file where it's pulling "Payment HW" from?
