Power Query Custom Columns

0 votes

So, I am new to power query and I followed up a tutorial video on YouTube and tried to add a custom column so I can use it to see week over week improvements. Now, I have the inline PowerQuery to automate my weekly reporting. The problem here is that the added custom column is not named "Week". It is called the name of the file, instead. From this webpage the second parameter is the column name. I don't really understand why the column name is the filename instead of the name "week".

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
Dec 12, 2018 in Power BI by Shubham
• 13,490 points
619 views

1 answer to this question.

0 votes

This call:

FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader)

replaces the column names you have with the values from the first row. 

Since you have your first value under the column as the filename -"Week", your table is using that filename as the column name.

This can be fixed 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
answered Dec 12, 2018 by Upasana
• 8,620 points

Related Questions In Power BI

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,490 points
3,226 views
+2 votes
3 answers

How to replace null values with custom values in Power BI(power query editor)?

Hi Nithin, To fill or replace the null or ...READ MORE

answered Mar 8, 2019 in Power BI by Cherukuri
• 33,030 points
152,461 views
+1 vote
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
• 52,360 points
1,417 views
0 votes
1 answer

Group by columns on Power BI

On power BI desktop, do the following Add ...READ MORE

answered Sep 25, 2018 in Power BI by Kalgi
• 52,360 points
686 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,160 points
3,789 views
0 votes
1 answer

Format Data to tabular structure in Excel - Power m Query - Excel

Hi, @Kiranbvsn You can check this out: https://www.howtoexcel.org/ ...READ MORE

answered Oct 27, 2020 in Power BI by Gitika
• 65,910 points
433 views
0 votes
1 answer

Using Multiple filters in DAX

CALCULATE is defined as CALCULATE(<expression>,<filter1>,<filter2>…) This means that you can ...READ MORE

answered Dec 22, 2020 in Power BI by Gitika
• 65,910 points
7,533 views
0 votes
1 answer

Average TIME Calculation differs between Excel and PowerBI

They are calculating different things. In Power ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,420 points
2,610 views
0 votes
1 answer

Power Query : Adding Columns and Multiple files

The following call, FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) shall replace the ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
1,421 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,183 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP