Power Query Challenge 6

My code for the solution:

Code: 
let    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceID", Int64.Type}, {"ItemID", type any}, {"Quantity", type any}, {"Price", type any}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"InvoiceID"}, {{"Rows", each _, type table [InvoiceID=number, ItemID=anynonnull, Quantity=anynonnull, Price=anynonnull]}}),
    #"Added ItemID to List" = Table.AddColumn(#"Grouped Rows", "ItemID to List", each Table.TransformColumns(
Table.TransformColumnTypes([Rows], {{"ItemID", type text}}, "en-US"),
{{"ItemID", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}})),
    #"Added Quantity to List" = Table.AddColumn(#"Added ItemID to List", "Quantity to List", each Table.TransformColumns(
Table.TransformColumnTypes([ItemID to List], {{"Quantity", type text}}, "en-US"),
{{"Quantity", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}})),
    #"Added Custom" = Table.AddColumn(#"Added Quantity to List", "Price to List", each Table.TransformColumns(
Table.TransformColumnTypes([Quantity to List], {{"Price", type text}}, "en-US"),
{{"Price", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}})),
    #"Expanded Price to List" = Table.ExpandTableColumn(#"Added Custom", "Price to List", {"ItemID", "Quantity", "Price"}, {"ItemID", "Quantity", "Price"}),
    #"Added List Zip" = Table.AddColumn(#"Expanded Price to List", "List Zip", each List.Zip({[ItemID],[Quantity],[Price]})),
    #"Expanded List Zip" = Table.ExpandListColumn(#"Added List Zip", "List Zip"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded List Zip", {"List Zip", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "List Zip", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"List Zip.1", "List Zip.2", "List Zip.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"List Zip.1", Int64.Type}, {"List Zip.2", Int64.Type}, {"List Zip.3", type number}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"InvoiceID", "List Zip.1", "List Zip.2", "List Zip.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"List Zip.1", "Item ID"}, {"List Zip.2", "Quantity"}, {"List Zip.3", "Price"}})
in
    #"Renamed Columns"

And my file: View attachment challenge 6.xlsx
 
Ivan's solution

Hi Ken,

Pls, see my idea below.
Tried to keep it simple, so anyone could understand every step.
Suppose, someone can come up with a mega-function solution :)

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceID", type text}, {"ItemID", type text}, {"Quantity", type text}, {"Price", type text}}),
    
    // count number of Items in each line
    #"Added Count" = Table.AddColumn(#"Changed Type", "Count", each Text.Length( [ItemID] ) - Text.Length( Text.Replace( [ItemID], "#(lf)", "" ) )),


    // repeat invoice ID required # times
    #"Added Invoice" = Table.AddColumn(#"Added Count", "Invoice", each [InvoiceID] & Text.Repeat( "#(lf)" & [InvoiceID], [Count] ) ),


    // add lists of invoices / items / prices
    #"Added Invoices List" = Table.AddColumn(#"Added Invoice", "Invoices", each Text.Split( [Invoice], "#(lf)" ) ),
    #"Added Items List" = Table.AddColumn(#"Added Invoices List", "Items", each Text.Split( [ItemID], "#(lf)" )),
    #"Added Qty List" = Table.AddColumn(#"Added Items List", "Qty", each Text.Split( [Quantity], "#(lf)" )),
    #"Added Prices List" = Table.AddColumn(#"Added Qty List", "Prices", each Text.Split( [Price], "#(lf)" )),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Prices List",{"Invoices", "Items", "Qty", "Prices"}),


    // Zip added lists
    #"Added Zip" = Table.AddColumn(#"Removed Other Columns", "Zip", each List.Zip( { [Invoices], [Items], [Qty], [Prices] } )),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Zip",{"Zip"}),


    // expand zipped list
    #"Expanded Zip" = Table.ExpandListColumn(#"Removed Other Columns1", "Zip"),
    // extract values from zipped list, use ";" as a delimiter
    #"Extracted Values" = Table.TransformColumns(#"Expanded Zip", {"Zip", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),


    // split values
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Zip", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Zip.1", "Zip.2", "Zip.3", "Zip.4"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Zip.1", "Invoice"}, {"Zip.2", "Item"}, {"Zip.3", "Quantity"}, {"Zip.4","Price"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Quantity", type number}, {"Price", type number}})
in
    #"Changed Type1"

Ivan
 

Attachments

  • challenge 6 - solution Ivan.xlsx
    22.2 KB · Views: 56
Split each of the three columns to rows separately and add an index column then close to connection, and finally merge the three queries:

Code: 
let
    Source = Table.NestedJoin(Item, {"Index"}, Quantity, {"Index"}, "Quantity", JoinKind.LeftOuter),
    #"Expanded Quantity" = Table.ExpandTableColumn(Source, "Quantity", {"Quantity"}, {"Quantity.1"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Quantity", {"Index"}, Price, {"Index"}, "Price", JoinKind.LeftOuter),
    #"Expanded Price" = Table.ExpandTableColumn(#"Merged Queries", "Price", {"Price"}, {"Price.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Price",{{"Quantity.1", "Quantity"}, {"Price.1", "Price"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Index"})
in
    #"Removed Columns"
 

Attachments

  • PQ Challenge 6 AliGW.xlsx
    23 KB · Views: 97
Leveraging m functionality to keep it short and sweet.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ItemID", type text}, {"Quantity", type text}, {"Price", type text}}),
    Transform = Table.TransformColumns(#"Changed Type",{{"ItemID", each Text.Split(_,"#(lf)"), type list}, {"Quantity", each Text.Split(_,"#(lf)"), type list}, {"Price", each Text.Split(_,"#(lf)"), type list}}),
    MergeLists = Table.AddColumn(Transform, "MergedLists", each List.Transform(List.Positions([ItemID]), 
(Pos) => {[ItemID]{Pos}} & {[Quantity]{Pos}} & {[Price]{Pos}} ), type list),
    #"Removed Columns" = Table.RemoveColumns(MergeLists,{"ItemID", "Quantity", "Price"}),
    #"Expanded MergedLists" = Table.ExpandListColumn(#"Removed Columns", "MergedLists"),
    AddColumns = List.Accumulate({0,1,2}, #"Expanded MergedLists", (s,c) => Table.AddColumn(s, {"ItemID", "Quantity", "Price"}{c} , each Number.From([MergedLists]{c}),  type number  )),
    #"Removed Columns1" = Table.RemoveColumns(AddColumns,{"MergedLists"})
in
    #"Removed Columns1"
 
Second attempt to post this:

Code: 
let    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceID", type text}, {"ItemID", type text}, {"Quantity", type text}, {"Price", type text}}),
    #"Added All data" = Table.AddColumn(#"Changed Type", "All data", each 
        List.Zip({
            Text.Split([ItemID],"#(lf)"),
            Text.Split([Quantity],"#(lf)"),
            Text.Split([Price],"#(lf)")
        })),
    #"Removed Other Columns" = Table.SelectColumns(#"Added All data",{"InvoiceID", "All data"}),
    #"Expanded All data" = Table.ExpandListColumn(#"Removed Other Columns", "All data"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded All data", {"All data", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "All data", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"ItemID", "Quantity", "Price"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ItemID", Int64.Type}, {"Quantity", Int64.Type}, {"Price", type number}})
in
    #"Changed Type1"
 
Several solutions
1. Almost UI solution
Code: 
let    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceID", type text}, {"ItemID", type text}, {"Quantity", type text}, {"Price", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index", "InvoiceID"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Unpivoted Other Columns", {{"Value", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"InvoiceID", "Attribute"}, {{"tbl", each Table.AddIndexColumn(_, "Idx",1,1), type table [InvoiceID=text, Index=number, Attribute=text, Value=number, Idx=number]}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"tbl"}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Removed Other Columns", "tbl", {"InvoiceID", "Index", "Attribute", "Value", "Idx"}, {"InvoiceID", "Index", "Attribute", "Value", "Idx"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded {0}", List.Distinct(#"Expanded {0}"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index", "Idx"})
in
    #"Removed Columns"
Only step # "Grouped Rows" required manual intervention (red part below)
#"Grouped Rows" = Table.Group(#"Changed Type1", {"InvoiceID", "Attribute"}, {{"tbl", each Table.AddIndexColumn(_, "Idx",1,1), type table [InvoiceID=text, Index=number, Attribute=text, Value=number[/COLOR], Idx=number]}})

And three solutions with a bit of M-code :typing: :peace:
First
Code: 
let    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceID", type text}, {"ItemID", type text}, {"Quantity", type text}, {"Price", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Tables", each Table.FromColumns(List.Transform(List.Skip(Record.ToList(_),1), each Lines.FromText(_)), List.Skip(Table.ColumnNames(Source),1) )  ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"InvoiceID", "Tables"}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Removed Other Columns", "Tables", {"ItemID", "Quantity", "Price"}, {"ItemID", "Quantity", "Price"})
in
    #"Expanded {0}"
Second
Code: 
let    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceID", type text}, {"ItemID", type text}, {"Quantity", type text}, {"Price", type text}}),
    #"Added Custom" = Table.Combine(Table.AddColumn(#"Changed Type", "tbl", each Table.FromColumns(List.Transform(Record.ToList(_), each Lines.FromText(_)), Table.ColumnNames(Source) ) )[tbl]),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"InvoiceID"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"InvoiceID", Int64.Type}, {"ItemID", Int64.Type}, {"Quantity", Int64.Type}, {"Price", type number}})
in
    #"Changed Type1"
And third (with Lines.FromText M-function)
Code: 
let    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceID", type text}, {"ItemID", type text}, {"Quantity", type text}, {"Price", type text}}),
    Lines = List.Transform(Table.ColumnNames(#"Changed Type"), each {_, Lines.FromText}),
    NewTbl = Table.TransformColumns(#"Changed Type", Lines),
    ToRec = Table.ToRecords(NewTbl),
    ListOfTables = List.Transform(ToRec, each Table.FromColumns(Record.ToList(_), Record.FieldNames(_))),
    Almost = Table.Combine(ListOfTables),
    #"Filled Down" = Table.FillDown(Almost,{"InvoiceID"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"InvoiceID", Int64.Type}, {"ItemID", Int64.Type}, {"Quantity", Int64.Type}, {"Price", type number}})
in
    #"Changed Type1"
 

Attachments

  • challenge 6 - solutions Bill Szysz.xlsx
    32.2 KB · Views: 70
Code: 
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(Source, {{"ItemID", type text}}, "en-US"), {{"ItemID", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ItemID"),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"ItemID","InvoiceID"}),
    z1 = Table.AddIndexColumn(#"Removed Other Columns", "Index", 0, 1),

    #"Split Column by Delimiter2" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(Source, {{"Quantity", type text}}, "en-US"), {{"Quantity", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type number) meta [Serialized.Text = true] in type {itemType}}}), "Quantity"),
    #"Removed Other Columns2" = Table.SelectColumns(#"Split Column by Delimiter2",{"Quantity"}),
    z2 = Table.AddIndexColumn(#"Removed Other Columns2", "Index", 0, 1),

    #"Split Column by Delimiter3" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(Source, {{"Price", type text}}, "en-US"), {{"Price", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type number) meta [Serialized.Text = true] in type {itemType}}}), "Price"),
    #"Removed Other Columns3" = Table.SelectColumns(#"Split Column by Delimiter3",{"Price"}),
    z3 = Table.AddIndexColumn(#"Removed Other Columns3", "Index", 0, 1),

    z4 = Table.NestedJoin(z1,{"Index"},z2,{"Index"},"z1",JoinKind.LeftOuter),
    z5 = Table.NestedJoin(z4,{"Index"},z3,{"Index"},"z4",JoinKind.LeftOuter),
    #"Expanded z1" = Table.ExpandTableColumn(z5, "z1", {"Quantity"}, {"Quantity"}),
    #"Expanded z4" = Table.ExpandTableColumn(#"Expanded z1", "z4", {"Price"}, {"Price"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded z4",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"InvoiceID", "ItemID", "Quantity", "Price"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Quantity", type number}, {"Price", type number}})
in #"Changed Type"
 
UI only - and it could have been better...

Not an ideal solution but it works - I've simply broken down the text and then, based on the index from the split columns, I've reconstructed it again - only through UI.:



Code: 
let
    Source = Excel.Workbook(File.Contents("C:\Users\Vitali.Burla\OneDrive - Adastra, s.r.o\Documents\Power BI Files - Personal\challenge 6.xlsx"), null, true),
    Data_Table = Source{[Item="Data",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data_Table,{{"InvoiceID", Int64.Type}, {"ItemID", type any}, {"Quantity", type any}, {"Price", type any}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"ItemID", type text}}, "en-GB"), "ItemID", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"ItemID.1", "ItemID.2", "ItemID.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ItemID.1", Int64.Type}, {"ItemID.2", Int64.Type}, {"ItemID.3", Int64.Type}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type1", {{"Quantity", type text}}, "en-GB"), "Quantity", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Quantity.1", "Quantity.2", "Quantity.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Quantity.1", Int64.Type}, {"Quantity.2", Int64.Type}, {"Quantity.3", Int64.Type}, {"Price", type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Price", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Price.1", "Price.2", "Price.3"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Price.1", type number}, {"Price.2", type number}, {"Price.3", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type3", {"InvoiceID", "Quantity.1", "Quantity.2", "Quantity.3", "Price.1", "Price.2", "Price.3"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Item"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"InvoiceID", "Item", "Quantity.1", "Quantity.2", "Quantity.3", "Price.1", "Price.2", "Price.3", "Attribute"}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Reordered Columns", {"InvoiceID", "Item", "Price.1", "Price.2", "Price.3", "Attribute"}, "Attribute.1", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Columns1",{{"Value", "Quantity"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"InvoiceID", "Item", "Quantity", "Price.1", "Price.2", "Price.3", "Attribute", "Attribute.1"}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Reordered Columns1", {"Price.1", "Price.2", "Price.3"}, "Attribute.2", "Value"),
    #"Extracted Last Characters" = Table.TransformColumns(#"Unpivoted Only Selected Columns", {{"Attribute", each Text.End(_, 1), type text}}),
    #"Extracted Last Characters1" = Table.TransformColumns(#"Extracted Last Characters", {{"Attribute.1", each Text.End(_, 1), type text}}),
    #"Extracted Last Characters2" = Table.TransformColumns(#"Extracted Last Characters1", {{"Attribute.2", each Text.End(_, 1), type text}}),
    #"Added Custom" = Table.AddColumn(#"Extracted Last Characters2", "Keep1", each ([Attribute]=[Attribute.1])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Keep2", each ([Attribute.1]=[Attribute.2])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Keep1] = true) and ([Keep2] = true)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"InvoiceID", "Item", "Quantity", "Value"})
in
    #"Removed Other Columns"
 
Code: 
[COLOR=#373737][FONT='inherit']let[/FONT][/COLOR]
[COLOR=#373737][FONT='inherit']Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],[/FONT][/COLOR]
[COLOR=#373737][FONT='inherit']mChgTyp = Table.TransformColumnTypes(Source,{{"ItemID", type text}, {"Quantity", type text}, {"Price", type text}, {"InvoiceID", type text}}),[/FONT][/COLOR]
[COLOR=#373737][FONT='inherit']mSplitColDL = Table.TransformColumns(mChgTyp, {{"ItemID", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv)},{"Quantity", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv)},{"Price", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv)}}),[/FONT][/COLOR]
[COLOR=#373737][FONT='inherit']mCombineCols = Table.CombineColumns(mSplitColDL,{"ItemID", "Quantity","Price"}, each List.Transform(List.Zip(_), each Record.FromList(_,{"ItemID", "Quantity","Price"})),"Merged"),[/FONT][/COLOR]
[COLOR=#373737][FONT='inherit']mXpandListCol = Table.ExpandListColumn(mCombineCols, "Merged"),[/FONT][/COLOR]
[COLOR=#373737][FONT='inherit']mXpandRecCol = Table.ExpandRecordColumn(mXpandListCol, "Merged", {"ItemID", "Quantity", "Price"}, {"ItemID", "Quantity", "Price"}),[/FONT][/COLOR]
[COLOR=#373737][FONT='inherit']mChgTyp1 = Table.TransformColumnTypes(mXpandRecCol,{{"ItemID", type text}, {"Quantity", type number}, {"Price", type number}})[/FONT][/COLOR]
[COLOR=#373737][FONT='inherit']in[/FONT][/COLOR]
[COLOR=#373737][FONT='inherit']mChgTyp1[/FONT][/COLOR]
 
Hey Bill, these are very neat solutions :) I tried also with Record from List but it seems I did something wrong so I reverted back to UI.
 
Last edited:
Hi Ken,

not the shortest one, but it works.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
     #"Split Column by Delimiter" =  Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(Source,  {{"ItemID", type text}}, "de-DE"), {{"ItemID",  Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType =  (type nullable text) meta [Serialized.Text = true] in type  {itemType}}}), "ItemID"),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Quantity", "Price"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1)
in
    #"Added Index"
 
let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
     #"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceID",  Int64.Type}, {"ItemID", type any}, {"Quantity", type any}, {"Price",  type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"InvoiceID", "ItemID", "Price"}),
     #"Split Column by Delimiter" =  Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#"Removed  Columns", {{"Quantity", type text}}, "de-DE"), {{"Quantity",  Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType =  (type nullable text) meta [Serialized.Text = true] in type  {itemType}}}), "Quantity"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Quantity", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1)
in
    #"Added Index"
 
let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
     #"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceID",  Int64.Type}, {"ItemID", type any}, {"Quantity", type any}, {"Price",  type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"InvoiceID", "ItemID", "Quantity"}),
     #"Split Column by Delimiter" =  Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#"Removed  Columns", {{"Price", type text}}, "de-DE"), {{"Price",  Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType =  (type nullable text) meta [Serialized.Text = true] in type  {itemType}}}), "Price"),
    #"Added Index" = Table.AddIndexColumn(#"Split Column by Delimiter", "Index", 0, 1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Index",{{"Price", type text}})
in
    #"Changed Type1"
 
let
    Source = Table.NestedJoin(Data, {"Index"}, #"Data (2)", {"Index"}, "Data (2)", JoinKind.LeftOuter),
    #"Expanded {0}" = Table.ExpandTableColumn(Source, "Data (2)", {"Quantity"}, {"Quantity"})
in
    #"Expanded {0}"
 
let
    Source = Table.NestedJoin(Merge1, {"Index"}, #"Data (3)", {"Index"}, "Data (3)", JoinKind.LeftOuter),
    #"Expanded {0}" = Table.ExpandTableColumn(Source, "Data (3)", {"Price"}, {"Price"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded {0}",{"Index"})
in
    #"Removed Columns"
 

Attachments

  • Excel PQ - Power Query Challenge 6 - Solution of Pinarello.xlsx
    24.8 KB · Views: 17
Nice challenge, Ken!
Quite often met in the practice, at least mine practice.

My solution in the file attached
 

Attachments

  • PQ challenge #6 Kolyu Minevski.xlsx
    26.6 KB · Views: 20
Hello
Variant
Code: 
let    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    tableHeader = Table.ColumnNames(Source),
    setTextTypeToAll = Table.TransformColumnTypes(Source,{{"InvoiceID", type text}, {"ItemID", type text}, {"Quantity", type text}, {"Price", type text}}),
    maker = Table.AddColumn(setTextTypeToAll, "temp", (rec) =>
        Table.FromColumns(List.Transform(Record.FieldValues(rec), each Text.Split(_, "#(lf)")), tableHeader)
    ),
    result = Table.FillDown(Table.Combine(maker[temp]), tableHeader),
    return = Table.TransformColumnTypes(result,{{"InvoiceID", Int64.Type}, {"ItemID", Int64.Type}, {"Quantity", Int64.Type}, {"Price", Number.Type}}, "en-US")
in
    return
Regards,
 
Hi everyone, this is my first post here, I'm just starting to learn PQ and M, so be merciful ;-)
I split the task in 4 queries, split merged cells, unpivoted, inserted a nested index after grouping by invoice id, and finally merged them all together. Quite happy with myself to be honest lol
 

Attachments

  • challenge 6_klingklang.xlsx
    24.3 KB · Views: 12
klingklang said:
Hi everyone, this is my first post here, I'm just starting to learn PQ and M, so be merciful ;-)
Click to expand...

Welcome to the challenges! It's all about learning, so have a look through the other solutions and see what works best.

My (two) answers are attached, with a bunch more data that I used for performance testing.
 

Attachments

  • challenge 6 - solution.xlsx
    463.1 KB · Views: 58
Ooh, I feel quite proud that one of your two approaches is the same as the one I chose to use - I must be learning! :)

Your all-in-one attempt is interesting - I got close, but didn't know how to do the index towards the end. This is very useful - thanks!
 
Hello
I tested solutions for speed. Bellow it 's a macro for generating a test dataset. I had 200000 source rows and 900761 target rows for testing.
Code: 
Option Explicit
Option Base 1
 
Public Sub generateSample()
    Const rowCount As Long = 200000, maxItemCount = 8
    Dim invoceId As Long, itemId As Long, pFuncs As WorksheetFunction
    Dim prices() As Variant, quantities() As Variant
    Dim outArr() As String, itemCount As Long, i As Long, k As Long
    Dim itemIds As String, quantityIds As String, priceIds As String
    Set pFuncs = Application.WorksheetFunction
    quantities = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10")
    prices = Array("11.23", "25.24", "37.33", "47.94", "52.32", "67.24", "75.82", "84.38", "98.09", "108.47")
    ReDim outArr(1 To rowCount, 1 To 4)
    For i = 1 To rowCount
        invoceId = invoceId + 1
        outArr(i, 1) = Format$(invoceId, "000000")
        itemCount = pFuncs.RandBetween(1, maxItemCount)
        itemIds = "": quantityIds = "": priceIds = ""
        For k = 1 To itemCount
            itemIds = itemIds & IIf(itemIds = "", "", vbLf) & Format$(itemId + k, "0000000")
            quantityIds = quantityIds & IIf(quantityIds = "", "", vbLf) & quantities(pFuncs.RandBetween(1, 10))
            priceIds = priceIds & IIf(priceIds = "", "", vbLf) & prices(pFuncs.RandBetween(1, 10))
        Next
        outArr(i, 2) = itemIds: outArr(i, 3) = quantityIds: outArr(i, 4) = priceIds
        itemId = itemId + itemCount
    Next
    ActiveWorkbook.Worksheets("Sheet1").Range("A6").Resize(rowCount, 4).Value = outArr
End Sub
Result: GroupAndSplit 78.24, SplitAndMerge 39.39, mine 24.75 seconds.
Conclusion: Do not use mouse hardcode in M :)
Regards,
 
Ken Puls said:
Welcome to the challenges! It's all about learning, so have a look through the other solutions and see what works best.
Click to expand...
Thanks! I obviously missed some tricks, but I'm glad that the nested index technique was part of your one-query solution as well. Next time I'll do better! Maybe ;)
 
AndVgri said:
Hello
I tested solutions for speed. Bellow it 's a macro for generating a test dataset. I had 200000 source rows and 900761 target rows for testing.
Click to expand...

Thank you for posting. Maybe you missed the point of the thread? It's not a request for help, but a PowerQuery challenge. The idea is to solve the problem using PowerQuery and it's just for fun. :)
 
You must log in or register to reply here.
Back
Top