Is there a simple way to Pivot a 1ColumnTable to a 4ColumnTable ?
//Ola
//Ola
Code:
//(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"