cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ranga_88
Helper I
Helper I

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 - 3.png

 

image 2 2.PNG

 

image 3

 4.PNG

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!

1 ACCEPTED SOLUTION

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

 

mussaenda_0-1627471442119.png

 

View solution in original post

13 REPLIES 13
ranga_88
Helper I
Helper I

@v-shex-msft  @mussaenda 

Herewith i have attached my sample data set

Data set Click here 

 

i need sheet name as a one column

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"

 

 

mussaenda_0-1627470171923.png

 

if still unclear,

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

 

 

Hope this idea help you.

 

@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

mussaenda_0-1627470171923.png

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

 

mussaenda_0-1627471442119.png

 

View solution in original post

@mussaenda    it's working thanks for your support

 

is this possible to share the screen shot of condition column or pbix file  bcz i have litle bit confusion in conditional column

if [Column2] = null 
then null 
else 

if Text.Contains(
    Text.Upper([Column1]), "EMPLOYEE NAME"
    )
    then "SheetName"

else 
[Name]

 

hope this helps

v-shex-msft
Community Support
Community Support

Hi @ranga_88,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
ranga_88
Helper I
Helper I

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

ranga_88
Helper I
Helper I

@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

 

5.PNG

 

mussaenda
Resident Rockstar
Resident Rockstar

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. 

@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

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

@mussaenda    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

if new sheet created means example (Aug 2021)  if i refresh power bi  i need aug 2021 data

 

6.PNG

 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors