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

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.