Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Merged cell

Dear                                                          

 

i have excel table contains Item number, Department and Location having thousands of rows. Department and Location column contains merged cell as shownn:

 

 

i need prepare data for power bi, in the format: 

 how can i achieve this above in Power bi/ Excel ??

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    First, you can replace the empty values in excel cells with the special string(For example: NULL) in your source excel file just as below screenshot. Power BI will keep the value for the first row of the merged cell and the rest ones will be null when you load the data with merged cell. So we need to deal with these empty values first in excel file...

    Then we can use Fill down feature in Power Query Editor to fill the values for the "left ones" in merge cell...

    Fill down

    The full applied codes as follow:

    let
    Source = Excel.Workbook(File.Contents("\your directory\data.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Item number", type text}, {"Department", type text}, {"Location", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type1",{"Department"}),
    #"Filled Down1" = Table.FillDown(#"Filled Down",{"Location"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down1","NULL","",Replacer.ReplaceValue,{"Department", "Location"})
in
    #"Replaced Value"

    Best Regards

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    First, you can replace the empty values in excel cells with the special string(For example: NULL) in your source excel file just as below screenshot. Power BI will keep the value for the first row of the merged cell and the rest ones will be null when you load the data with merged cell. So we need to deal with these empty values first in excel file...

    Then we can use Fill down feature in Power Query Editor to fill the values for the "left ones" in merge cell...

    Fill down

    The full applied codes as follow:

    let
    Source = Excel.Workbook(File.Contents("\your directory\data.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Item number", type text}, {"Department", type text}, {"Location", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type1",{"Department"}),
    #"Filled Down1" = Table.FillDown(#"Filled Down",{"Location"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down1","NULL","",Replacer.ReplaceValue,{"Department", "Location"})
in
    #"Replaced Value"

    Best Regards

  • rbriga's avatar
    rbriga
    Icon for Impactful Individual rankImpactful Individual
    4 years ago

    To the best of my knowledge (some searching included), you can't do it with escel or Power BI, as tables unmerge those cells.

    Some VBA scripts are availble, but your data also contains "legitimate" blanks, so that eliminates that option.