Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I'd like some help in obtaining the 2 transformations framed in red (see screenshot)
Here's the M code I'm stuck on
Thanks in advance
Regards
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"Count", each Table.RemoveColumns( _, "Index")}})
in
#"Grouped Rows"
Column1Column2
160 mm | 412,37 EUR |
AB 105 - 163 mm | 11 |
170 mm | 734,56 EUR |
AB 115 - 173 mm | 11 |
180 mm | 467,33 EUR |
AB 125 - 183 mm | 22 |
190 mm | 632,50 EUR |
AB 135 - 193 mm | 33 |
200 mm | 532,15 EUR |
AB 145 - 203 mm | 44 |
210 mm | 347,45 EUR |
AB 155 - 213 mm | 55 |
220 mm | 842,45 EUR |
AB 165 - 223 mm | 66 |
230 mm | 236,70 EUR |
AB 175 - 233 mm | 77 |
Solved! Go to Solution.
Try:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {
{"Pivot",(t)=>
let
#"Remove Index" = Table.RemoveColumns(t,"Index"),
#"Unpivot" = Table.UnpivotOtherColumns(#"Remove Index",{},"Attribute","Value"),
#"Col Names" =
Table.RemoveColumns(
Table.TransformColumns(
Table.AddIndexColumn(#"Unpivot","New Cols",1,1,Int64.Type),
{"New Cols", each Number.ToText(_, "'Column'0")}),
{"Attribute"}),
Pivot = Table.Pivot(#"Col Names",#"Col Names"[New Cols],"New Cols","Value")
in
#"Pivot"}
}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Index"}),
#"Expanded Pivot" = Table.ExpandTableColumn(#"Removed Columns", "Pivot", {"Column1", "Column2", "Column3", "Column4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Pivot",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}})
in
#"Changed Type1"
Try:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {
{"Pivot",(t)=>
let
#"Remove Index" = Table.RemoveColumns(t,"Index"),
#"Unpivot" = Table.UnpivotOtherColumns(#"Remove Index",{},"Attribute","Value"),
#"Col Names" =
Table.RemoveColumns(
Table.TransformColumns(
Table.AddIndexColumn(#"Unpivot","New Cols",1,1,Int64.Type),
{"New Cols", each Number.ToText(_, "'Column'0")}),
{"Attribute"}),
Pivot = Table.Pivot(#"Col Names",#"Col Names"[New Cols],"New Cols","Value")
in
#"Pivot"}
}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Index"}),
#"Expanded Pivot" = Table.ExpandTableColumn(#"Removed Columns", "Pivot", {"Column1", "Column2", "Column3", "Column4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Pivot",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}})
in
#"Changed Type1"