Forum Discussion
Need help on M code as sheet name as a column name.
Hi Experts,
Need help on M code that i need sheet name as a column. "Sheet Name" as a column in Data Table. When Data column expanded sheet name should be added . Thanks in advance
Expected Output :
Thanks for the reply from SamInogic , please allow me to provide another insight:
Hi, powerbi9988
Could you please let us know if SamInogic‘s response resolved your issue? If it did, kindly accept it as the solution.
To facilitate your understanding, I have created the following example:
1.Firstly, I created the following example and created three sheets:
2.Secondly, you need to perform the import in Power Query:
3.Below are the modifications to the M language for my data:
let Source = Excel.Workbook(File.Contents("C:\Users\v-linyulu\Desktop\test_data.xlsx"), null, true), RenamedColumns = Table.RenameColumns(Source, {{"Name", "SheetName"}}), AddSheetNameColumn = Table.AddColumn(RenamedColumns, "DataWithSheetName", each Table.AddColumn([Data], "SheetName", (x) => [SheetName])), #"Expanded DataWithSheetName" = Table.ExpandTableColumn(AddSheetNameColumn, "DataWithSheetName", {"Column1", "Column2", "SheetName"}, {"DataWithSheetName.Column1", "DataWithSheetName.Column2", "DataWithSheetName.SheetName"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded DataWithSheetName",{"Data"}) in #"Removed Columns"4.You can modify it for your data in the Advanced Editor:
5.Below is the result derived from ll's solution. I hope it meets your expectations:
Please find the attached pbix relevant to the case.
Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.We recommend uploading it to GitHub and sharing the link with us.When uploading a file, please be careful to delete sensitive information.
For questions about uploading data, you can try the following links:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Solved: How to upload PBI in Community - Microsoft Fabric Community
Best Regards,
Leroy Lu
2 Replies
Hi,
We are a bit unclear about your requirement.
However, based on our current understanding, it seems you would like to include the sheet name as a column in your data table.
To achieve this, you can write an M code expression in Power Query. Please follow the steps below:
Navigate to Power Query.
Select your table.
Open the Advanced Editor and paste the following code:
let
Source = Excel.CurrentWorkbook(),
RenamedColumns = Table.RenameColumns(Source, {{"SheetNam", "SheetName"}}),
AddSheetNameColumn = Table.AddColumn(RenamedColumns, "DataWithSheetName", each Table.AddColumn([Data], "SheetName", (x) => [SheetName])),
ExpandedData = Table.ExpandTableColumn(AddSheetNameColumn, "DataWithSheetName")
in
ExpandedData
This code will rename the column to SheetName and add the sheet name as a column to the expanded data table.
Let us know if this aligns with your requirements or if additional clarification is needed. We are happy to assist further.
Thanks!
Thanks for the reply from SamInogic , please allow me to provide another insight:
Hi, powerbi9988
Could you please let us know if SamInogic‘s response resolved your issue? If it did, kindly accept it as the solution.
To facilitate your understanding, I have created the following example:
1.Firstly, I created the following example and created three sheets:
2.Secondly, you need to perform the import in Power Query:
3.Below are the modifications to the M language for my data:
let Source = Excel.Workbook(File.Contents("C:\Users\v-linyulu\Desktop\test_data.xlsx"), null, true), RenamedColumns = Table.RenameColumns(Source, {{"Name", "SheetName"}}), AddSheetNameColumn = Table.AddColumn(RenamedColumns, "DataWithSheetName", each Table.AddColumn([Data], "SheetName", (x) => [SheetName])), #"Expanded DataWithSheetName" = Table.ExpandTableColumn(AddSheetNameColumn, "DataWithSheetName", {"Column1", "Column2", "SheetName"}, {"DataWithSheetName.Column1", "DataWithSheetName.Column2", "DataWithSheetName.SheetName"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded DataWithSheetName",{"Data"}) in #"Removed Columns"4.You can modify it for your data in the Advanced Editor:
5.Below is the result derived from ll's solution. I hope it meets your expectations:
Please find the attached pbix relevant to the case.
Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.We recommend uploading it to GitHub and sharing the link with us.When uploading a file, please be careful to delete sensitive information.
For questions about uploading data, you can try the following links:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Solved: How to upload PBI in Community - Microsoft Fabric Community
Best Regards,
Leroy Lu
