Forum Discussion
Adding a new column with value from a specific cell. Then append all sheets from a single excel
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.
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 reorderColsYou 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
8 Replies
Hi NS_powerbi ,
Can you provide a fairly detailed example of your source data/structure please?
Please remove any sensitive information first.
Pete
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.
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
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"Thanks Ankit. But I don't have the data in this format.
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.
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 reorderColsYou 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
Thanks a lot Pete. I was able to implement it on my dataset perfectly.
