PowerQuery - Pivot 1ColumnTable to 4ColumnTable

OlaSa

New Member
Joined
Mar 22, 2015
Messages
23
Is there a simple way to Pivot a 1ColumnTable to a 4ColumnTable ?
//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"
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Solved. Just needed to understand how Table.Pivot works in PQ.
//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),
    #"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"
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top