Adding a new column with value from a specific cell. Then append all sheets from a single excel

[NS_powerbi]

Hi everyone, 

I have a single excel file with multiple excel sheets. Now I am able to combine all excel sheets and then clean the data. However, I have one specific thing which needs to be done, where the data can't be formatted later. Each sheet has data for pupils. 

So all my sheets have something like this at the start of the sheet where the number in the bracket is pupil id:

Pupil name (115)

This is followed by column headers and then the entire data. 

I want to create two new columns in each sheet where the value of 1 column is Pupil name and the value of column 2 is pupil id.  

And then I want to append all sheets. 

So data looks something like - 

Pupil Name Pupil ID Record1 Record2

Alice             115       xxx          yyy

Alice             115       xxtx         nnyyy    

Alice             115       sbx          yll       

Sid                 275      aaa          bbb

Sid                 275      aa1          b2b

Where records of Alice are coming from sheet 1 and records of Sid are coming from sheet 2.

Can someone please help with it?

Thanks a lot.

[NS_powerbi]

Here is what I get after cleaning the data in power query editor

Oakley (250)	Null	Null	Null	Null
Surname	Forename	Emp ID	DOB	Gender
Steve	Alex	11556	09/06/1982	M
Max	Zach	76454	04/05/1970	M
Rob	Bob	66660	02/03/1980	M
Seattle (402)	Null	Null	Null	Null
Bab	Homey	88554	01/02/1982	F
Clar	Maxine	55223	07/01/1976	F

There are 40 shops. And the number of employees are different in each sheet.  Now I want to use the row with the surname as header, amd create two new columns to enter the shop name and the shop id. To get result like this. 

Shop	Shop id	Surname	Forename	Emp ID	DOB	Gender
Oakley	250	Steve	Alex	11556	09/06/1982	M
Oakley	250	Max	Zach	76454	04/05/1970	M
Oakley	250	Rob	Bob	66660	02/03/1980	M
Seattle	402	Bab	Homey	88554	01/02/1982	F
Seattle	402	Clar	Maxine	55223	07/01/1976	F

Hope this makes my problem clearer. Thanks a lot. Really appreciate it. 

[BA_Pete · Accepted solution]

Hi @NS_powerbi ,

Paste the following code over the default code in Advanced Editor of a new blank query to see the steps I took:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY9ND4IwDIb/CuGkCcnGYAOOIqIeCIncJBymNtHIhyFo4N/bApGDy9L3bfa0a/PcTPWzhMFYCcnXpmUut7ByM3u3ta4A07hpYba76mUcIzRRGmLcQ32DdsI7+BCxKaFHsW0pFSoPGFfMDnyBSTKSiSbgrK93FE+50iXOZVwi5/Efd2ou6MMxKjz0wgXjDrVbsAx015VgrFwu/rcINZUfmgoGVN+X0282w07zVPEIbkvdUk/dP2raQ0ohHEI9hjQOpia0+AI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    cleanBlanks = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column2", "Column3", "Column4", "Column5"}),
    addShopToSplit = Table.AddColumn(cleanBlanks, "Shop", each if [Column1] <> null and [Column3] = null then [Column1]
else null),
    splitToShopShopId = Table.SplitColumn(addShopToSplit, "Shop", Splitter.SplitTextByEachDelimiter({" ("}, QuoteStyle.Csv, false), {"Shop.1", "Shop.2"}),
    repCloseBracket = Table.ReplaceValue(splitToShopShopId,")","",Replacer.ReplaceText,{"Shop.2"}),
    fillDownShopShopId = Table.FillDown(repCloseBracket,{"Shop.1", "Shop.2"}),
    filterNullEmpId = Table.SelectRows(fillDownShopShopId, each ([Column3] <> null)),
    repR1ShopHeader = Table.ReplaceValue(filterNullEmpId, each [Shop.1], each if [Column1] = "Surname" then "Shop" else [Shop.1],Replacer.ReplaceText,{"Shop.1"}),
    repR1ShopIdHeader = Table.ReplaceValue(repR1ShopHeader, each [Shop.2], each if [Column1] = "Surname" then "Shop Id" else [Shop.2],Replacer.ReplaceText,{"Shop.2"}),
    promHeads = Table.PromoteHeaders(repR1ShopIdHeader, [PromoteAllScalars=true]),
    reorderCols = Table.ReorderColumns(promHeads,{"Shop", "Shop Id", "Surname", "Forename", "Emp ID", "DOB", "Gender"})
in
    reorderCols

You probably won't need the 'cleanBlanks' step as I understand your actual data contains pure nulls.

This gives me the following output:

As I mentioned before, this is completely bespoke to the exact situation that you have presented and, therefore, you will need to understand the principles and functions used in order to amend it to a new scenario if required.

Pete

[NS_powerbi]

Thanks a lot Pete. I was able to implement it on my dataset perfectly. 

[NS_powerbi]

@BA_Pete , the above example wasn't the actual data I am using. So I will give another example to make it clearer.

Suppose a business has several shops. The excel file is for that business. In the excel file, there are several sheets with each shop being of a specific shop. 

Each sheet has the shop information stored in a specific cell and is of the format - ShopName(Shop_ID). This is at the same place in all sheets - suppose at Cell (5,B). 

Now I want to create a single sheet with information from all the sheets appended. However, I want the employees information matched to the shop they work in. So I need to add two new columns and get the Shop information from Cell (5,B). So it would be like this 

Shop Name	Shop ID	Employee Name	Employee ID
OAKLEY	252	Peter	1151
OAKLEY	252	Sam	6542
OAKLEY	252	Maddy	7766
Seattle	157	Zach	1252
Seattle	157	Rosy	3522

So here the first 3 rows are coming from sheet 1 and the next two rows are coming from sheet 2. The Shop Name and Shop ID are the two columns I want to add and fill with values from a specific cell. 

[BA_Pete]

Hi @NS_powerbi ,

Thanks for the update.

I understand the scenario, it's just that I need to see exactly what these sheets look like once imported into Power Query to be able to help you.

This isn't likely to be a generic 'click this then this' solution, it will need to be specifically tailored to your exact situation.

The types of things I will need to see:

- Exactly which table cell the ShopName(Shop_ID) value appears in Power Query.

- Whether it appears in the same cell for every sheet.

- Whether the main data displays directly below it, or off to the side.

- How many rows there are between the Shop(ID) cell and the main data.

- Whether the employee data you need is part of the main data, or where it comes from if not.

- What the structure of the main data is e.g. what order the columns are in, number of rows etc.

- And so on...

Pete

[AnkitKukreja]

Hi! @NS_powerbi 

What Pete mentioned was to share a sanitized sample data and your expected result, that helps us to answer your queries much faster.

I hope I've understood your problem here. You've let's say 3 excel sheet with the data as shown below, but these are 3 different sheets.

So, what you can do is keep all the sheets in the same folder and choose the Get Data --> and Folder option and Power BI does the magic for you.

I'm sharing the m code for your reference as well. I hope this helps, please let me know if you've anyother questions.

let
Source = Folder.Files("C:\Users\ankit kukreja\Desktop\Power BI Community"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Shop Name", type text}, {"Shop ID", Int64.Type}, {"Employee Name", type text}, {"Employee ID", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"})
in
#"Removed Columns"

[NS_powerbi]

Thanks Ankit. But I don't have the data in this format. 

[BA_Pete]

Hi @NS_powerbi ,

Can you provide a fairly detailed example of your source data/structure please?

Please remove any sensitive information first.

Pete