Need help on M code as sheet name as a column name.

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