[Table] as data value

Hi Anonymous 

Open Advanced editor, write this code in

let

    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\4\4.27\4.27.xlsx"), null, true),

    Sheet7_Sheet = Source{[Item="Sheet7",Kind="Sheet"]}[Data],

    #"Promoted Headers" = Table.PromoteHeaders(Sheet7_Sheet, [PromoteAllScalars=true]),

    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"workitem", Int64.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Table.FromValue({"mm",Table.FromValue({"a","b"}), "yy"})),

    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Value"}, {"Custom.Value"}),

    #"Added Index" = Table.AddIndexColumn(#"Expanded Custom", "Index", 0, 1),

    Source1 = Table.SelectRows(#"Added Index", each ([Index] = 1)),

    #"Expanded Custom.Value1" = Table.ExpandTableColumn(Source1, "Custom.Value", {"Value"}, {"Custom.Value.Value"}),

    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Custom.Value1",{{"Custom.Value.Value", "Custom.Value"}}),

    #"Appended Query1" = Table.Combine({#"Renamed Columns1",#"Added Index" }),

    Source2 = Table.SelectRows(#"Added Index", each ([Index] = 4)),

    #"Expanded Custom.Value2" = Table.ExpandTableColumn(Source2, "Custom.Value", {"Value"}, {"Custom.Value.Value"}),

    #"Renamed Columns2" = Table.RenameColumns(#"Expanded Custom.Value2",{{"Custom.Value.Value", "Custom.Value"}}),

    #"Appended Query2" = Table.Combine({#"Renamed Columns2", #"Appended Query1"}),

    Source3 = Table.SelectRows(#"Added Index", each ([Index] = 7)),

    #"Expanded Custom.Value3" = Table.ExpandTableColumn(Source3, "Custom.Value", {"Value"}, {"Custom.Value.Value"}),

    #"Renamed Columns3" = Table.RenameColumns(#"Expanded Custom.Value3",{{"Custom.Value.Value", "Custom.Value"}}),

    #"Appended Query3" = Table.Combine({#"Renamed Columns3", #"Appended Query2"}),

    #"Changed Type2" = Table.TransformColumnTypes(#"Appended Query3",{{"Custom.Value", type text}}),

    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type2", {{"Custom.Value", null}}),

    #"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each [Custom.Value] <> null and [Custom.Value] <> "")

in

#"Filtered Rows"

Best Regards
Maggie