Page 1 of 4 1 2 3 ... LastLast
Results 1 to 10 of 31

Thread: Power Query Challenge 6

  1. #1
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,324
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider

    Power Query Challenge 6



    Register for a FREE account, and/
    or Log in to avoid these ads!

    This is the sample data for Power Query Challenge 6, which you'll find posted here: https://www.excelguru.ca/blog/2019/0...y-challenge-6/
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  2. #2
    Neophyte ehans's Avatar
    Join Date
    Aug 2019
    Posts
    1
    Articles
    0
    Excel Version
    Office 365, Insider Builds
    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: challenge 6.xlsx

  3. #3
    Neophyte IvanBond's Avatar
    Join Date
    Aug 2019
    Posts
    1
    Articles
    0
    Excel Version
    Excel 365

    Lightbulb 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
    Attached Files Attached Files

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,235
    Articles
    0
    Excel Version
    Office 365 Subscription
    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"
    Attached Files Attached Files
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    26
    Articles
    0
    Excel Version
    Office 365
    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"

  6. #6
    Neophyte lind25's Avatar
    Join Date
    Aug 2019
    Posts
    1
    Articles
    0
    Excel Version
    O365
    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"

  7. #7
    Acolyte Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    96
    Articles
    0
    Excel Version
    Excel 365
    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, Idx=number]}})

    And three solutions with a bit of M-code
    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"
    Attached Files Attached Files

  8. #8
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    78
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    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"

  9. #9
    Neophyte vitali_burla's Avatar
    Join Date
    Nov 2018
    Posts
    2
    Articles
    0
    Excel Version
    365

    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"

  10. #10
    Neophyte sgbhide's Avatar
    Join Date
    Aug 2019
    Posts
    1
    Articles
    0
    Excel Version
    O365 Insider Monthly Targeted
    Code:
    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    mChgTyp = Table.TransformColumnTypes(Source,{{"ItemID", type text}, {"Quantity", type text}, {"Price", type text}, {"InvoiceID", type text}}),
    mSplitColDL = Table.TransformColumns(mChgTyp, {{"ItemID", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv)},{"Quantity", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv)},{"Price", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv)}}),
    mCombineCols = Table.CombineColumns(mSplitColDL,{"ItemID", "Quantity","Price"}, each List.Transform(List.Zip(_), each Record.FromList(_,{"ItemID", "Quantity","Price"})),"Merged"),
    mXpandListCol = Table.ExpandListColumn(mCombineCols, "Merged"),
    mXpandRecCol = Table.ExpandRecordColumn(mXpandListCol, "Merged", {"ItemID", "Quantity", "Price"}, {"ItemID", "Quantity", "Price"}),
    mChgTyp1 = Table.TransformColumnTypes(mXpandRecCol,{{"ItemID", type text}, {"Quantity", type number}, {"Price", type number}})
    in
    mChgTyp1

Page 1 of 4 1 2 3 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •