[SIZE=1]// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Type = Table.TransformColumnTypes(Source,{{"Specification", type text}, {"Material", type any}, {"Change Number", type text}, {"Valid From", type date}, {"Valid To", type date}, {"Description", type text}, {"Property Tree", type text}, {"Level 1", type text}, {"Level 2", type text}, {"Level 3", type text}, {"Specification2", type any}, {"Property", type text}, {"Field", type text}, {"Sequence", Int64.Type}, {"Validity Area", type text}, {"Difference", type any}, {"Value", type text}}),
Replace = Table.ReplaceValue(Table.SelectRows(Table.ReplaceValue(Table.ReplaceValue(Table.ReplaceValue(Table.ReplaceValue(Type,"",null,Replacer.ReplaceValue,{"Material", "Level 3", "Specification2", "Difference"}),"<blank>",null,Replacer.ReplaceValue,{"Value"}),"",null,Replacer.ReplaceValue,{"Value"}),"",null,Replacer.ReplaceValue,{"Specification", "Material", "Change Number", "Valid From", "Valid To", "Description", "Property Tree", "Level 1", "Level 2", "Level 3", "Specification2", "Property", "Field", "Sequence", "Validity Area", "Difference", "Value"}), each ([Specification] <> null)),"",null,Replacer.ReplaceValue,{"Specification", "Material", "Change Number", "Valid From", "Valid To", "Description", "Property Tree", "Level 1", "Level 2", "Level 3", "Specification2", "Property", "Field", "Sequence", "Validity Area", "Difference", "Value"}),
Group = Table.Group(Replace, {"Specification", "Material", "Change Number", "Valid From", "Valid To", "Description", "Property Tree", "Specification2", "Difference"}, {{"Count", each _, type table}}),
Level1 = Table.SplitColumn(Table.TransformColumns(Table.AddColumn(Group, "Level 1", each List.Distinct(Table.Column([Count],"Level 1"))), {"Level 1", each Text.Combine(List.Transform(_, Text.From), "="), type text}), "Level 1", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Level 1.1", "Level 1.2", "Level 1.3", "Level 1.4", "Level 1.5"}),
Level2 = Table.SplitColumn(Table.TransformColumns(Table.AddColumn(Level1, "Level 2", each List.Distinct(Table.Column([Count],"Level 2"))), {"Level 2", each Text.Combine(List.Transform(_, Text.From), "="), type text}), "Level 2", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Level 2.1", "Level 2.2", "Level 2.3", "Level 2.4", "Level 2.5", "Level 2.6"}),
Level3 = Table.SplitColumn(Table.TransformColumns(Table.AddColumn(Level2, "Level 3", each List.Distinct(Table.Column([Count],"Level 3"))), {"Level 3", each Text.Combine(List.Transform(_, Text.From), "="), type text}), "Level 3", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Level 3.1", "Level 3.2"}),
Property = Table.SplitColumn(Table.TransformColumns(Table.AddColumn(Level3, "Property", each List.Distinct(Table.Column([Count],"Property"))), {"Property", each Text.Combine(List.Transform(_, Text.From), "="), type text}), "Property", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Property.1", "Property.2", "Property.3", "Property.4", "Property.5", "Property.6", "Property.7", "Property.8", "Property.9", "Property.10", "Property.11", "Property.12", "Property.13"}),
Field = Table.SplitColumn(Table.TransformColumns(Table.AddColumn(Property, "Field", each List.Distinct(Table.Column([Count],"Field"))), {"Field", each Text.Combine(List.Transform(_, Text.From), "="), type text}), "Field", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Field.1", "Field.2", "Field.3", "Field.4"}),
Sequence = Table.SplitColumn(Table.TransformColumns(Table.AddColumn(Field, "Sequence", each List.Distinct(Table.Column([Count],"Sequence"))), {"Sequence", each Text.Combine(List.Transform(_, Text.From), "="), type text}), "Sequence", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Sequence.1", "Sequence.2", "Sequence.3", "Sequence.4", "Sequence.5", "Sequence.6"}),
ValidityArea = Table.SplitColumn(Table.TransformColumns(Table.AddColumn(Sequence, "Validity Area", each List.Distinct(Table.Column([Count],"Validity Area"))), {"Validity Area", each Text.Combine(List.Transform(_, Text.From), "="), type text}), "Validity Area", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Validity Area.1", "Validity Area.2"}),
Value = Table.SplitColumn(Table.TransformColumns(Table.AddColumn(ValidityArea, "Value", each List.Distinct(Table.Column([Count],"Value"))), {"Value", each Text.Combine(List.Transform(_, Text.From), "="), type text}), "Value", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5", "Value.6", "Value.7", "Value.8", "Value.9", "Value.10", "Value.11", "Value.12", "Value.13", "Value.14", "Value.15", "Value.16", "Value.17", "Value.18", "Value.19", "Value.20", "Value.21", "Value.22", "Value.23", "Value.24", "Value.25", "Value.26", "Value.27", "Value.28"})
in
Value[/SIZE]
