Forum Discussion

NS_powerbi's avatar
NS_powerbi
New Member
4 years ago
Solved

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, whe...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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's avatar
NS_powerbi
New Member
4 years ago

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 NameShop IDEmployee NameEmployee ID
OAKLEY252Peter1151
OAKLEY252Sam6542
OAKLEY252Maddy7766
Seattle157Zach1252
Seattle157Rosy3522

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's avatar
    BA_Pete
    Super User
    4 years ago

    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's avatar
    AnkitKukreja
    Super User
    4 years ago

    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's avatar
      NS_powerbi
      New Member
      4 years ago

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