locked
Table Transformation RRS feed

  • Question

  • Hi: I need assistance please to convert a table as per shown below. In the (PO Number) column first 6 rows (same 6 numbers in one cell) repeated in 6 rows. The first number belongs to first row, second number belongs to second row etc. 

    Same applies for the second group of numbers.

    Input
    Date Start Time End Time Total Billed Hours Total Charge Invoice Number PO Number
    13/09/18 17:30 05:30 12 517.95 Q058702 246662, 246663,246666,246667,246668, 246669
    14/09/18 17:30 05:30 12 531.97 Q058702 246662, 246663,246666,246667,246668, 246669
    15/09/18 17:30 05:30 12 633.18 Q058702 246662, 246663,246666,246667,246668, 246669
    15/09/18 05:30 17:30 12 551.28 Q058702 246662, 246663,246666,246667,246668, 246669
    16/09/18 17:30 05:30 12 634.05 Q058702 246662, 246663,246666,246667,246668, 246669
    16/09/18 05:30 17:30 12 729.96 Q058702 246662, 246663,246666,246667,246668, 246669
    13/10/18 17:30 05:30 12 517.95 Q058702 246670, 246671,246672
    14/10/18 17:30 05:30 12 531.97 Q058702 246670, 246671,246672
    15/10/18 17:30 05:30 12 633.18 Q058702 246670, 246671,246672
                 
    OutPut            
    Date Start Time End Time Total Billed Hours Total Charge Invoice Number PO Number
    13/09/18 17:30 05:30 12 517.95 Q058702 246662
    14/09/18 17:30 05:30 12 531.97 Q058702 246663
    15/09/18 17:30 05:30 12 633.18 Q058702 246666
    15/09/18 05:30 17:30 12 551.28 Q058702 246667
    16/09/18 17:30 05:30 12 634.05 Q058702 246668
    16/09/18 05:30 17:30 12 729.96 Q058702 246669
    13/10/18 17:30 05:30 12 517.95 Q058702 246670
    14/10/18 17:30 05:30 12 531.97 Q058702 246671
    15/10/18 17:30 05:30 12 633.18 Q058702 246672

    Wednesday, May 29, 2019 2:23 AM

Answers

  • Slightly different approach.

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabela2"]}[Content],
        #"Grouped Rows" = Table.Group(Source, {"PO Number"}, {  
                                                              {"lst1", each List.FirstN(Table.ToColumns(_), List.Count(Table.ColumnNames(Source))-1), type list },
                                                              {"lst2", each List.Transform(Text.Split(_[PO Number]{0}, ","),  Text.Trim), type list}
                                                             }),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "TableOK", each Table.FromColumns([lst1] & {[lst2]}, Table.ColumnNames(Source))),
        Combine = Table.Combine(#"Added Custom"[TableOK]),
        #"Changed Type" = Table.TransformColumnTypes(Combine,{
                                                              {"Date", type date}, {"Start Time", type time},
                                                              {"End Time", type time}, {"Total Billed Hours", Int64.Type},
                                                              {"Total Charge", type number}, {"Invoice Number", type text},
                                                              {"PO Number", Int64.Type}
                                                             })
    in
        #"Changed Type"

    • Proposed as answer by anthony34 Monday, June 3, 2019 5:46 AM
    • Marked as answer by M.Awal Monday, June 3, 2019 10:56 AM
    Saturday, June 1, 2019 9:59 PM
  • ()=> replaces "each"

    it is mandatory when we need to nest several each.

    the first (o)=> is used to refer to the table "content"

    then the each is used to refer the rows of the table

    the {0} only transforms the 1-item "list"  into a "text" type because "text" type is what we want

    • Edited by anthony34 Saturday, June 1, 2019 7:23 PM
    • Proposed as answer by anthony34 Monday, June 3, 2019 5:46 AM
    • Marked as answer by M.Awal Monday, June 3, 2019 10:56 AM
    Saturday, June 1, 2019 7:18 PM
  • Thanks
    • Marked as answer by M.Awal Sunday, June 2, 2019 10:10 AM
    Sunday, June 2, 2019 10:10 AM
  • Dear M.Awal
    I wonder how you could consider your reply "thanks" as the answer to your question.
    Bill and I provided you with a proper answer. These should be flagged as answer.

    • Marked as answer by M.Awal Monday, June 3, 2019 10:56 AM
    Monday, June 3, 2019 5:46 AM

All replies

  • let
        Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
        Group = Table.Group(#"Source", {"PO Number"}, {{"Content", each _, type table}}),
        TransformCol = Table.TransformColumns(#"Group", {
                {"Content", (o)=> let
                    AddIndex =Table.AddIndexColumn(o, "Index", 0, 1),
                    ExtractPONumber = Table.AddColumn(#"AddIndex", "new PO Number", each Text.Trim(List.Range(Text.Split([PO Number], ","), [Index], 1) {0})),
                    RemCol = Table.RemoveColumns(#"ExtractPONumber", {"Index", "PO Number"}),
                    RenCol = Table.RenameColumns(#"RemCol", {"new PO Number","PO Number"})
                    in #"RenCol",
                type table}}
        ),
        Combine = Table.Combine(TransformCol[Content]),
        ChType = Table.TransformColumnTypes(Combine,{
                {"Date", type date}, {"Start Time", type time},
                {"End Time", type time}, {"Total Billed Hours", Int64.Type},
                {"Total Charge", type number}, {"Invoice Number", type text},
                {"PO Number", Int64.Type}
        })
    in #"ChType"

    • Edited by anthony34 Tuesday, June 4, 2019 6:33 AM
    Wednesday, May 29, 2019 6:42 AM
  • Thank you very much for looking into it. Is the “o” inside ()=> refers to variable? And the ()=> replacement for each? Also not clear on this specific reference {0} , why it’s only referring to “1st item”
    Friday, May 31, 2019 11:27 PM
  • ()=> replaces "each"

    it is mandatory when we need to nest several each.

    the first (o)=> is used to refer to the table "content"

    then the each is used to refer the rows of the table

    the {0} only transforms the 1-item "list"  into a "text" type because "text" type is what we want

    • Edited by anthony34 Saturday, June 1, 2019 7:23 PM
    • Proposed as answer by anthony34 Monday, June 3, 2019 5:46 AM
    • Marked as answer by M.Awal Monday, June 3, 2019 10:56 AM
    Saturday, June 1, 2019 7:18 PM
  • Slightly different approach.

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabela2"]}[Content],
        #"Grouped Rows" = Table.Group(Source, {"PO Number"}, {  
                                                              {"lst1", each List.FirstN(Table.ToColumns(_), List.Count(Table.ColumnNames(Source))-1), type list },
                                                              {"lst2", each List.Transform(Text.Split(_[PO Number]{0}, ","),  Text.Trim), type list}
                                                             }),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "TableOK", each Table.FromColumns([lst1] & {[lst2]}, Table.ColumnNames(Source))),
        Combine = Table.Combine(#"Added Custom"[TableOK]),
        #"Changed Type" = Table.TransformColumnTypes(Combine,{
                                                              {"Date", type date}, {"Start Time", type time},
                                                              {"End Time", type time}, {"Total Billed Hours", Int64.Type},
                                                              {"Total Charge", type number}, {"Invoice Number", type text},
                                                              {"PO Number", Int64.Type}
                                                             })
    in
        #"Changed Type"

    • Proposed as answer by anthony34 Monday, June 3, 2019 5:46 AM
    • Marked as answer by M.Awal Monday, June 3, 2019 10:56 AM
    Saturday, June 1, 2019 9:59 PM
  • Thanks
    • Marked as answer by M.Awal Sunday, June 2, 2019 10:10 AM
    Sunday, June 2, 2019 10:10 AM
  • Dear M.Awal
    I wonder how you could consider your reply "thanks" as the answer to your question.
    Bill and I provided you with a proper answer. These should be flagged as answer.

    • Marked as answer by M.Awal Monday, June 3, 2019 10:56 AM
    Monday, June 3, 2019 5:46 AM
  • Sorry for the delay in getting back to you. Thank you very much for your time and effort, greatly appreciated. Like the way you came up with the solution.
    Monday, June 3, 2019 10:59 AM
  • Monday, June 3, 2019 11:13 AM
  • Hi Anthony : I don't know what I am doing wrong. Why I am getting same time across all the rows. Its not matching the input.

    Thanks

    Screenshot

    Friday, June 21, 2019 9:42 AM
  • Please see below.

    https://1drv.ms/x/s!Amc8fiGpDxekhXZpA7-3QiAeHt86

    Friday, June 21, 2019 9:53 AM
  • The issue is with Excel online.
    I downloaded your file, and it produces the expected results with Excel:

    I am not used with Excel online. Maybe someone else knows why it is not working with Excel online while it is working with Excel


    • Edited by anthony34 Friday, June 21, 2019 10:28 AM
    Friday, June 21, 2019 10:17 AM
  • That's strange. I remember when you sent me the solution initially, it was all good. Today when I am looking at it again I am thinking, may be I had missed checking the date column

    Friday, June 21, 2019 10:26 AM