PowerQuery - Pivot 1ColumnTable to 4ColumnTable

[OlaSa]

Is there a simple way to Pivot a 1ColumnTable to a 4ColumnTable ?
//Ola

//(page as number) as table =>
 let
//Source = Web.Page(Web.Contents("http://www.cafekartan.se/Stockholm/?sida=" & Number.ToText(page))),
Source = Web.Page(Web.Contents("http://www.cafekartan.se/Stockholm/?sida=1")),
    Data0 = Source{0}[Data],
    Children = Data0{0}[Children],
    Children1 = Children{1}[Children],
    Children2 = Children1{2}[Children],
    Children3 = Children2{0}[Children],
    Children4 = Children3{0}[Children],
    Children5 = Children4{0}[Children],
    Children6 = Children5{2}[Children],
    #"Removed Other Columns" = Table.SelectColumns(Children6,{"Children"}),
    #"Expanded Children" = Table.ExpandTableColumn(#"Removed Other Columns", "Children", {"Kind", "Name", "Children", "Text"}, {"Kind", "Name", "Children.1", "Text"}),
    #"Expanded Children.1" = Table.ExpandTableColumn(#"Expanded Children", "Children.1", {"Kind", "Name", "Children", "Text"}, {"Kind.1", "Name.1", "Children", "Text.1"}),
    #"Expanded Children1" = Table.ExpandTableColumn(#"Expanded Children.1", "Children", {"Kind", "Name", "Children", "Text"}, {"Kind.2", "Name.2", "Children.1", "Text.2"}),
    #"Expanded Children.2" = Table.ExpandTableColumn(#"Expanded Children1", "Children.1", {"Kind", "Name", "Children", "Text"}, {"Kind.3", "Name.3", "Children", "Text.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Children.2",{"Kind", "Name", "Kind.1", "Name.1", "Kind.2", "Name.2", "Kind.3", "Name.3", "Children", "Text.1", "Text"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Text.3", "Text.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Text"),
    #"Filtered Rows" = Table.SelectRows(#"Merged Columns", each ([Text] <> "" and [Text] <> "lägga till det")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Inserted Modulo", each Number.Mod([Index], 4), type number),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Modulo",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Inserted Modulo", "Text"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Text"}, "Attribute", "Value"),
    #"Removed Columns2" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns2", "Custom", each if [Value] = 0 then "Name" else if [Value] = 1 then "Address" else if [Value] = 2 then "Score" else if [Value] = 3 then "Votes" else null ),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Conditional Column",{"Custom", "Text", "Value"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Reordered Columns1",{"Value"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns3","Snittbetyg: ","",Replacer.ReplaceText,{"Text"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," röster","",Replacer.ReplaceText,{"Text"})
in
    #"Replaced Value1"

 

[OlaSa]

Solved. Just needed to understand how Table.Pivot works in PQ.
//Ola

//(page as number) as table => 
let
//    Source = Web.Page(Web.Contents("http://www.cafekartan.se/Stockholm/?sida=" & Number.ToText(page))),
    Source = Web.Page(Web.Contents("http://www.cafekartan.se/Stockholm/?sida=1")),
    Data0 = Source{0}[Data],
    Children = Data0{0}[Children],
    Children1 = Children{1}[Children],
    Children2 = Children1{2}[Children],
    Children3 = Children2{0}[Children],
    Children4 = Children3{0}[Children],
    Children5 = Children4{0}[Children],
    Children6 = Children5{2}[Children],
    #"Removed Other Columns" = Table.SelectColumns(Children6,{"Children"}),
    #"Expanded Children" = Table.ExpandTableColumn(#"Removed Other Columns", "Children", {"Kind", "Name", "Children", "Text"}, {"Kind", "Name", "Children.1", "Text"}),
    #"Expanded Children.1" = Table.ExpandTableColumn(#"Expanded Children", "Children.1", {"Kind", "Name", "Children", "Text"}, {"Kind.1", "Name.1", "Children", "Text.1"}),
    #"Expanded Children1" = Table.ExpandTableColumn(#"Expanded Children.1", "Children", {"Kind", "Name", "Children", "Text"}, {"Kind.2", "Name.2", "Children.1", "Text.2"}),
    #"Expanded Children.2" = Table.ExpandTableColumn(#"Expanded Children1", "Children.1", {"Kind", "Name", "Children", "Text"}, {"Kind.3", "Name.3", "Children", "Text.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Children.2",{"Kind", "Name", "Kind.1", "Name.1", "Kind.2", "Name.2", "Kind.3", "Name.3", "Children", "Text.1", "Text"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Text.3", "Text.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Text"),
    #"Filtered Rows" = Table.SelectRows(#"Merged Columns", each ([Text] <> "" and [Text] <> "lägga till det")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Inserted Modulo", each Number.Mod([Index], 4), type number),
    #"Integer-Divided Column" = Table.TransformColumns(#"Inserted Modulo", {{"Index", each Number.IntegerDivide(_, 4), Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Integer-Divided Column", "Area", each if [Inserted Modulo] = 0 then "Namn" else if [Inserted Modulo] = 1 then "Adress" else if [Inserted Modulo] = 2 then "Snittbetyg" else if [Inserted Modulo] = 3 then "Röster" else null ),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column",{"Area", "Text", "Index", "Inserted Modulo"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns","Snittbetyg: ","",Replacer.ReplaceText,{"Text"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," röster","",Replacer.ReplaceText,{"Text"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Replaced Value1",{"Inserted Modulo"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Area]), "Area", "Text")
in
    #"Pivoted Column"