Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Add custom column with sheet name

is it possible to add a custom column with the sheet name as values? 

i have connected onedrive excel file every month they created one new sheet for example "January 2021, february 2021, March 2021, etc.. "  i have connected the excel sheet and merge all the sheets into one see below image 

image1 - 

 

image 2 

 

image 3

 

after i merge i have used first row as a header at the time names moving out (image2),  for next month im creating the new sheet means at the time im getting error (image 3)?  

 

how to add custom column with sheet name? 

Thank you very much in advance for any help on this topic!

Need Help
Show and Tell
  • mussaenda's avatar
    mussaenda
    5 years ago

    In that case, you can add a custom column, where you will create a rule.

    like if Column2 = null then null else if Column1 contains Employee Name then Name else Name.

     

    You can then remove the Name Column, and then promote your headers as you want

     

     

13 Replies

  • mussaenda's avatar
    mussaenda
    Community Champion
    5 years ago

    It is hard to tell why there is error if we cannot see your data transformation.

    But based on your 3rd image, it shows you promoted headers but Jan 2021 is not present.

    Try deleting ("Jan 2021",) from the Formula Bar.

     

    Hope this helps. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      mussaenda   first image list of sheets, im merging all the sheets using red highlighted place

      2 imge after i mergeing all the sheets to one sheet after that im getting results ,  then im using promoted header means name moving out feb 2021 is promoted as header,  next month im creating Mar 2021 sheet then i refresh power bi means im getting error  (image 3) just example

       

      i need add custom column with sheet name?   im trying name column based but  not work  any other possiblre way

      • mussaenda's avatar
        mussaenda
        Community Champion
        5 years ago

        Arrange your rows accordingly, Before promoting your rows into headers,

        create a custom column to get the Sheet Name then make sure rows without the sheet name is null then fill down. 

         

        If still doesnt help, give me sample data to help you to transform them .

         

        Thank you

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    mussaenda    Not working,     This is the sample dataset

     

    Database - May 2021
    Employee name Project/ Non Project CAPAProject Category RoleEfforts
    Test1Project NONPDR&D Support0.5
    Test2Project NONPDR&D Support0.2
    Test3Project NONPDR&D Support1
    Test4Project NOInnovationR&D Support1
    Test5Project NONPDR&D Support1
    Test6Project NONPDR&D Support1
    Test7Project NONPDR&D Support1
    Test8Project NONPDR&D Support1
    Test9Project NONPDR&D Support1
    Test10Project NONPDR&D Support0.9
    Test11Project NOInnovationR&D Support0.1

     

     

     

    Database - June 2021
    Employee name Project/ Non Project CAPAProject Category RoleEfforts
    Test1Project NONPDR&D Support0.5
    Test2Project NONPDR&D Support0.2
    Test3Project NONPDR&D Support1
    Test4Project NOInnovationR&D Support0.5
    Test5Project NONPDR&D Support0.5
    Test6Project NONPDR&D Support1
    Test7Project NONPDR&D Support1
    Test8Project NONPDR&D Support1
    Test9Project NONPDR&D Support1
    Test10Project NONPDR&D Support0.9
    Test11Project NOInnovationR&D Support0.1

     

     

     

     

    Database - July 2021
    Employee name Project/ Non Project CAPAProject Category RoleEfforts
    Test1Project NONPDR&D Support0.8
    Test2Project NONPDR&D Support0.2
    Test3Project NONPDR&D Support1
    Test4Project NOInnovationR&D Support0.5
    Test5Project NONPDR&D Support0.5
    Test6Project NONPDR&D Support1.6
    Test7Project NONPDR&D Support1.3
    Test8Project NONPDR&D Support1
    Test9Project NONPDR&D Support1
    Test10Project NONPDR&D Support0.9
    Test11Project NOInnovationR&D Support0.1

     

     

     i need result like this

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Not working ,   

     

    this is my samlple dataset,  3 month data 3 different sheets

    Database - May 2021
    Employee name Project/ Non Project CAPAProject Category RoleEfforts
    Test1Project NONPDR&D Support0.5
    Test2Project NONPDR&D Support0.2
    Test3Project NONPDR&D Support1
    Test4Project NOInnovationR&D Support1
    Test5Project NONPDR&D Support1
    Test6Project NONPDR&D Support1
    Test7Project NONPDR&D Support1
    Test8Project NONPDR&D Support1
    Test9Project NONPDR&D Support1
    Test10Project NONPDR&D Support0.9
    Test11Project NOInnovationR&D Support

    0.1

     

     

     

    Database - June 2021
    Employee name Project/ Non Project CAPAProject Category RoleEfforts
    Test1Project NONPDR&D Support0.5
    Test2Project NONPDR&D Support0.2
    Test3Project NONPDR&D Support1
    Test4Project NOInnovationR&D Support0.5
    Test5Project NONPDR&D Support0.5
    Test6Project NONPDR&D Support1
    Test7Project NONPDR&D Support1
    Test8Project NONPDR&D Support1
    Test9Project NONPDR&D Support1
    Test10Project NONPDR&D Support0.9
    Test11Project NOInnovationR&D Support0.1

     

     

     

    Database - July 2021
    Employee name Project/ Non Project CAPAProject Category RoleEfforts
    Test1Project NONPDR&D Support0.8
    Test2Project NONPDR&D Support0.2
    Test3Project NONPDR&D Support1
    Test4Project NOInnovationR&D Support0.5
    Test5Project NONPDR&D Support0.5
    Test6Project NONPDR&D Support1.6
    Test7Project NONPDR&D Support1.3
    Test8Project NONPDR&D Support1
    Test9Project NONPDR&D Support1
    Test10Project NONPDR&D Support0.9
    Test11Project NOInnovationR&D Support0.1

     

     

    i need one column , in that colum i need sheet name foe ex, May 2021, June 2021, July 2021

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous,

    According to the error message, it seems like these internal tables do not include compound column names so the function reference the detail records as the column header.
    For this scenario, I do not so recommend you to use detail records as column header, they will be changed when data source record changes and it will return an error about can't found out specific values.

    BTW, It will be help if you share some dummy data to test.

    How to Get Your Question Answered Quickly 

    Regards

    Xiaoxin Sheng

    • mussaenda's avatar
      mussaenda
      Community Champion
      5 years ago

      Is this okay for you?

       

      let
    Source = Excel.Workbook(File.Contents("<Input your file location here>\sampledata.xlsx"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Hidden] = false)),
    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"})
in
    #"Expanded Data"

       

       

       

      if still unclear,

      please review this link. Different approach but same output. 
      Get Sheet Names 

       

       

      Hope this idea help you.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        mussaenda   these are the stpes i have tried already not working ,  see the below image for your reference

         

        i want to remove first row then 2 row as a table header means name column title automatically went it out then august 2021 was appeared in header ,  if i add the next month data means at the time im getting error