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

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