Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear all ,
I have number of files in a folder , in which every file is data for each day .
Now my task I to combine these files and make a BI report .
I am using get data from folder option , which works great , but the issue is every file contains column header , i.e for file first file I promoted first row as header , but the column header from other files are still present in my data ,
Please help to remove column headers of files other than first file , so that I can move further for making report .
Thanks in advance
Please help @nickyvv @amitchandak
Regards ,
Sujit Thakur
Solved! Go to Solution.
@Sujit_Thakur - The key here is getting the Promoted Headers step into the correct query. The query you want to edit should be called Transform Sample File. Here is an example of the query you want to edit (below). Any steps that are in this query should be performed for each file in the folder.
@Greg_Deckler please help , I know you have answered such questions before , but to do it with excel files from a folder .
I tried loading a folder of excel files. It is loading fine. Only the first file's header is loaded. For rest of the files, it takes only the data.
Following is the auto-generated power query.
let
Source = Folder.Files("C:\Users\Sreenath\Documents\TestFiles"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Item", type text}, {"Qty", Int64.Type}})
in
#"Changed Type"
There is no step in the process where I had to explicitly "promote" the 1st row as headers. It was kind of automatic. But you mentioned that you did it.
Can you compare the power query here with the power query in your file? Can you post a screen shot of the step where you are promoting the first row as headers? It will help.
@Sujit_Thakur - The key here is getting the Promoted Headers step into the correct query. The query you want to edit should be called Transform Sample File. Here is an example of the query you want to edit (below). Any steps that are in this query should be performed for each file in the folder.
@Sujit_Thakur , Are you merging file. If you are merging. Then merge will take care of header ?
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
Dear @amitchandak ,
I am not mergibg , I am using get data and then selecting folder as a option and selecting a primary sheet type to extract data from all the files in folder
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |