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, 2019 in Power BI by Shubham
• 13,380 points
249 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, 2019 by Upasana
• 8,590 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, 2019 in Power BI by VNK
14,367 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,380 points
875 views
0 votes
0 answers
+1 vote
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
1,171 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,200 points
1,227 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, 2019 in Power BI by Upasana
• 8,590 points
82 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, 2019 in Power BI by Shubham
• 13,380 points
6,341 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, 2019 in Power BI by Upasana
• 8,590 points
823 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,590 points
127 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,590 points
84 views