Power Query : Adding Columns and Multiple files

0 votes

So, I'm using PowerQuery to automate weekly reporting. I'm attempting to add a Custom Column, through this tutorial video that I'm following, so that I could use it to see weekly progress. The issue I'm facing is that the column that is added is not named "Week" but instead the name of the file. 

let ExcelFile = (FilePath, FileName) =>
let
    Source = Folder.Files(FilePath),
    File = Source{[#"Folder Path"=FilePath,Name=FileName]}[Content],
    ImportedExcel = Excel.Workbook(File),
    Sheet1 = ImportedExcel{[Name="Page1_1"]}[Data],
    TableWithWeek = Table.AddColumn(Sheet1,"Week", each FileName),
    TableWithoutHeader = Table.Skip(TableWithWeek,3),
    FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader)
in
    FirstRowAsHeader
in
    ExcelFile


I'm curious so as to why is it so. Could anyone tell me?
Feb 14 in Power BI by Shubham
• 13,290 points
22 views

1 answer to this question.

0 votes

The following call,

FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader)

shall replace the column names you have with the values from the first row. Since the first value under the column "Week" is the filename, then your table will now use that filename as the column name.

You can fix this by adding the custom column after you use PromoteHeaders:

let ExcelFile = (FilePath, FileName) =>
    let
        Source = Folder.Files(FilePath),
        File = Source{[#"Folder Path"=FilePath,Name=FileName]}[Content],
        ImportedExcel = Excel.Workbook(File),
        Sheet1 = ImportedExcel{[Name="Page1_1"]}[Data],
        TableWithoutHeader = Table.Skip(Sheet1, 3),
        FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader),
        TableWithWeek = Table.AddColumn(FirstRowAsHeader,"Week", each FileName),
    in
        TableWithWeek
in
    ExcelFile

All the Best (:

answered Feb 14 by Upasana
• 8,530 points

Related Questions In Power BI

0 votes
2 answers

Power BI Dax Multiple IF AND Statements

HI I have Column Patient 12 13 14 15 18 IN Patient=12, 13 Out ...READ MORE

answered May 22 in Power BI by VNK
7,473 views
0 votes
1 answer

Select Columns from table instead of removing afterwards in Power Query

Try this. let db = Sql.Databases("sqlserver.database.url"){[Name="DatabaseName"]}[Data], Sales_vDimCustomer = ...READ MORE

answered Oct 31, 2018 in Power BI by Shubham
• 13,290 points
191 views
0 votes
1 answer

Unable to install connector for Power Bi and PostgreSQL

I think the problem is not at ...READ MORE

answered Aug 21, 2018 in Power BI by nirvana
• 3,060 points
602 views
0 votes
1 answer

Connect power bi desktop to dataset and create custom reports

Yes using Power BI REST API to ...READ MORE

answered Sep 18, 2018 in Power BI by Kalgi
• 40,440 points
109 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,190 points
190 views
0 votes
1 answer

Replace “yes” found in one list with the actual string from another list

Objective : The idea is to use List.Transform twice, ...READ MORE

answered Feb 19 in Power BI by Upasana
• 8,530 points
42 views
0 votes
1 answer

PowerQuery : How can I reference a cell's value?

You can do so using a named ...READ MORE

answered Feb 27 in Power BI by Shubham
• 13,290 points
311 views
0 votes
1 answer

Cumulative row data over last 12 months in Power Query

Basically, what you do is,  Add an Index,  Group ...READ MORE

answered Apr 1 in Power BI by Upasana
• 8,530 points
222 views
0 votes
1 answer

Power Query: Custom Columns

This call: FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) replaces the column names ...READ MORE

answered Dec 12, 2018 in Power BI by Upasana
• 8,530 points
39 views
0 votes
1 answer

Power Query: Adding a Special Case

Try adding a Group By with Minimum ...READ MORE

answered Dec 27, 2018 in Power BI by Upasana
• 8,530 points
39 views