Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
Herewith i have attached my sample data set
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"
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
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 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
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
Not working ,
this is my samlple dataset, 3 month data 3 different sheets
Database - May 2021 | |||||
Employee name | Project/ Non Project | CAPA | Project Category | Role | Efforts |
Test1 | Project | NO | NPD | R&D Support | 0.5 |
Test2 | Project | NO | NPD | R&D Support | 0.2 |
Test3 | Project | NO | NPD | R&D Support | 1 |
Test4 | Project | NO | Innovation | R&D Support | 1 |
Test5 | Project | NO | NPD | R&D Support | 1 |
Test6 | Project | NO | NPD | R&D Support | 1 |
Test7 | Project | NO | NPD | R&D Support | 1 |
Test8 | Project | NO | NPD | R&D Support | 1 |
Test9 | Project | NO | NPD | R&D Support | 1 |
Test10 | Project | NO | NPD | R&D Support | 0.9 |
Test11 | Project | NO | Innovation | R&D Support | 0.1
|
Database - June 2021 | |||||
Employee name | Project/ Non Project | CAPA | Project Category | Role | Efforts |
Test1 | Project | NO | NPD | R&D Support | 0.5 |
Test2 | Project | NO | NPD | R&D Support | 0.2 |
Test3 | Project | NO | NPD | R&D Support | 1 |
Test4 | Project | NO | Innovation | R&D Support | 0.5 |
Test5 | Project | NO | NPD | R&D Support | 0.5 |
Test6 | Project | NO | NPD | R&D Support | 1 |
Test7 | Project | NO | NPD | R&D Support | 1 |
Test8 | Project | NO | NPD | R&D Support | 1 |
Test9 | Project | NO | NPD | R&D Support | 1 |
Test10 | Project | NO | NPD | R&D Support | 0.9 |
Test11 | Project | NO | Innovation | R&D Support | 0.1 |
Database - July 2021 | |||||
Employee name | Project/ Non Project | CAPA | Project Category | Role | Efforts |
Test1 | Project | NO | NPD | R&D Support | 0.8 |
Test2 | Project | NO | NPD | R&D Support | 0.2 |
Test3 | Project | NO | NPD | R&D Support | 1 |
Test4 | Project | NO | Innovation | R&D Support | 0.5 |
Test5 | Project | NO | NPD | R&D Support | 0.5 |
Test6 | Project | NO | NPD | R&D Support | 1.6 |
Test7 | Project | NO | NPD | R&D Support | 1.3 |
Test8 | Project | NO | NPD | R&D Support | 1 |
Test9 | Project | NO | NPD | R&D Support | 1 |
Test10 | Project | NO | NPD | R&D Support | 0.9 |
Test11 | Project | NO | Innovation | R&D Support | 0.1 |
i need one column , in that colum i need sheet name foe ex, May 2021, June 2021, July 2021
@mussaenda Not working, This is the sample dataset
Database - May 2021 | |||||
Employee name | Project/ Non Project | CAPA | Project Category | Role | Efforts |
Test1 | Project | NO | NPD | R&D Support | 0.5 |
Test2 | Project | NO | NPD | R&D Support | 0.2 |
Test3 | Project | NO | NPD | R&D Support | 1 |
Test4 | Project | NO | Innovation | R&D Support | 1 |
Test5 | Project | NO | NPD | R&D Support | 1 |
Test6 | Project | NO | NPD | R&D Support | 1 |
Test7 | Project | NO | NPD | R&D Support | 1 |
Test8 | Project | NO | NPD | R&D Support | 1 |
Test9 | Project | NO | NPD | R&D Support | 1 |
Test10 | Project | NO | NPD | R&D Support | 0.9 |
Test11 | Project | NO | Innovation | R&D Support | 0.1 |
Database - June 2021 | |||||
Employee name | Project/ Non Project | CAPA | Project Category | Role | Efforts |
Test1 | Project | NO | NPD | R&D Support | 0.5 |
Test2 | Project | NO | NPD | R&D Support | 0.2 |
Test3 | Project | NO | NPD | R&D Support | 1 |
Test4 | Project | NO | Innovation | R&D Support | 0.5 |
Test5 | Project | NO | NPD | R&D Support | 0.5 |
Test6 | Project | NO | NPD | R&D Support | 1 |
Test7 | Project | NO | NPD | R&D Support | 1 |
Test8 | Project | NO | NPD | R&D Support | 1 |
Test9 | Project | NO | NPD | R&D Support | 1 |
Test10 | Project | NO | NPD | R&D Support | 0.9 |
Test11 | Project | NO | Innovation | R&D Support | 0.1 |
Database - July 2021 | |||||
Employee name | Project/ Non Project | CAPA | Project Category | Role | Efforts |
Test1 | Project | NO | NPD | R&D Support | 0.8 |
Test2 | Project | NO | NPD | R&D Support | 0.2 |
Test3 | Project | NO | NPD | R&D Support | 1 |
Test4 | Project | NO | Innovation | R&D Support | 0.5 |
Test5 | Project | NO | NPD | R&D Support | 0.5 |
Test6 | Project | NO | NPD | R&D Support | 1.6 |
Test7 | Project | NO | NPD | R&D Support | 1.3 |
Test8 | Project | NO | NPD | R&D Support | 1 |
Test9 | Project | NO | NPD | R&D Support | 1 |
Test10 | Project | NO | NPD | R&D Support | 0.9 |
Test11 | Project | NO | Innovation | R&D Support | 0.1 |
i need result like this
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 | CAPA | Project Category | Role | Efforts |
Test1 | Project | NO | NPD | R&D Support | 0.5 |
Test2 | Project | NO | NPD | R&D Support | 0.2 |
Test3 | Project | NO | NPD | R&D Support | 1 |
Test4 | Project | NO | Innovation | R&D Support | 1 |
Test5 | Project | NO | NPD | R&D Support | 1 |
Test6 | Project | NO | NPD | R&D Support | 1 |
Test7 | Project | NO | NPD | R&D Support | 1 |
Test8 | Project | NO | NPD | R&D Support | 1 |
Test9 | Project | NO | NPD | R&D Support | 1 |
Test10 | Project | NO | NPD | R&D Support | 0.9 |
Test11 | Project | NO | Innovation | R&D Support | 0.1 |
Database - June 2021 | |||||
Employee name | Project/ Non Project | CAPA | Project Category | Role | Efforts |
Test1 | Project | NO | NPD | R&D Support | 0.5 |
Test2 | Project | NO | NPD | R&D Support | 0.2 |
Test3 | Project | NO | NPD | R&D Support | 1 |
Test4 | Project | NO | Innovation | R&D Support | 0.5 |
Test5 | Project | NO | NPD | R&D Support | 0.5 |
Test6 | Project | NO | NPD | R&D Support | 1 |
Test7 | Project | NO | NPD | R&D Support | 1 |
Test8 | Project | NO | NPD | R&D Support | 1 |
Test9 | Project | NO | NPD | R&D Support | 1 |
Test10 | Project | NO | NPD | R&D Support | 0.9 |
Test11 | Project | NO | Innovation | R&D Support | 0.1 |
Database - July 2021 | |||||
Employee name | Project/ Non Project | CAPA | Project Category | Role | Efforts |
Test1 | Project | NO | NPD | R&D Support | 0.8 |
Test2 | Project | NO | NPD | R&D Support | 0.2 |
Test3 | Project | NO | NPD | R&D Support | 1 |
Test4 | Project | NO | Innovation | R&D Support | 0.5 |
Test5 | Project | NO | NPD | R&D Support | 0.5 |
Test6 | Project | NO | NPD | R&D Support | 1.6 |
Test7 | Project | NO | NPD | R&D Support | 1.3 |
Test8 | Project | NO | NPD | R&D Support | 1 |
Test9 | Project | NO | NPD | R&D Support | 1 |
Test10 | Project | NO | NPD | R&D Support | 0.9 |
Test11 | Project | NO | Innovation | R&D Support | 0.1 |
i need result like this
if new sheet created means example (Aug 2021) if i refresh power bi i need aug 2021 data
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |