Forum Discussion

GarethParry's avatar
GarethParry
Helper I
4 years ago

ColumnName issue when using Excel from Sharepoint

Hi,

 

I am connecting to a Excel sheet located on sharepoint.

 

The first row of the sheet has

A1 = ""

B1 = ""

C1 = "Data for June"

D1=""

 

when it brings in the data intially into power BI the column headings in Power BI are Column1,Column2,Data for June,Column4

 

How do i set it to not call the 3rd column "Data for June" but Column3 instead?

 

i think i have to change 

 

= Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))

 

but not sure to what

 

thanks

 

gareth

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    1. add index column

    2. replace value if index column=0/1(first row)

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    In your Transform file code

    Go to Transform menu - Use Headers as First Row

    Home menu - Remove Rows - Remove Top Rows - Give a value of 1

  • GarethParry's avatar
    GarethParry
    Helper I
    4 years ago

    Hi,

     

    Thanks for the quick reply. It addes the column headers at a point before i can remove the top row, or add the index column

     

    thanks

     

    Gareth

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi GarethParry ,

     

    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

     

    Best Regards,
    Eyelyn Qin