Power Query, What Am I missing here.
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: Power Query, What Am I missing here.

  1. #11
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,679
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Power Query, What Am I missing here.

    Could you explain what this code is supposed to do?

    Code:
    if [#"Segment No."] = 1 then Text.Trim (Text.Start([Application data],35)) else {[Index]-1}[#"PO Number"]
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  2. #12
    New Member
    Join Date
    Jul 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query, What Am I missing here.

    Quote Originally Posted by sandy666 View Post
    Could you explain what this code is supposed to do?

    Code:
    if [#"Segment No."] = 1 then Text.Trim (Text.Start([Application data],35)) else {[Index]-1}[#"PO Number"]
    I am looking at the column "Segment_No". if it is 1 then, the PO_Number=Application_data field is trimmed 1-35 , else PO_Number = PO_Number from the previous record

  3. #13
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,679
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Power Query, What Am I missing here.

    You calling PO Number itself
    How should it look like according to you?
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  4. #14
    New Member
    Join Date
    Jul 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query, What Am I missing here.

    Quote Originally Posted by sandy666 View Post
    You calling PO Number itself
    How should it look like according to you?
    number Counter Segment No. SAP segment name No. higher segment Hierarchy level Data Filter Value fo Length Application data PO_Number
    313708998 0 1 E1EDK09 0 1 1000 A11517 20190625190625 B A11517
    313708998 0 2 E1EDKA1 1 2 1000 LF FTL MFG A11517
    313708998 0 3 E1EDKA1 1 2 1000 YSCBW 7 A11517
    313708998 0 4 E1EDK11 1 2 1000 YHT1 1 Customer Horizon Start Date: 06/25/19 Customer Horizon End Date: 12/22/19 A11517
    313708998 0 5 E1EDK11 1 2 1000 YHT1 2 TMP 92 = 001 A11517
    313708998 0 6 E1EDP10 1 2 1000 BW K151860 001 0.000000 20190624 20190625 0.000000 03 534 A11517
    313708998 0 7 E1EDP16 6 3 1000 4W20190809 20190809 6 A11517
    313709018 0 1 E1EDK09 0 1 1000 A11517 20190625190625 B A11517
    313709018 0 2 E1EDKA1 1 2 1000 LF FTL MFG A11517
    313734018 0 1 E1EDK09 0 1 1000 A11596 20190625190625 B A11596
    313734018 0 1 E1EDKA1 0 1 1000 LF FTL MFG A11596

  5. #15
    New Member
    Join Date
    Jul 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query, What Am I missing here.

    IGNORE MY PREV MSG

    Quote Originally Posted by sandy666 View Post
    You calling PO Number itself
    How should it look like according to you?
    number Counter Segment No. SAP segment name No. higher segment Hierarchy level Data Filter Value fo Length Application data PO_Number
    313708998 0 1 E1EDK09 0 1 1000 A11517 20190625190625 B A11517
    313708998 0 2 E1EDKA1 1 2 1000 LF FTL MFG A11517
    313708998 0 3 E1EDKA1 1 2 1000 YSCBW 7 A11517
    313708998 0 4 E1EDK11 1 2 1000 YHT1 1 Customer Horizon Start Date: 06/25/19 Customer Horizon End Date: 12/22/19 A11517
    313708998 0 5 E1EDK11 1 2 1000 YHT1 2 TMP 92 = 001 A11517
    313708998 0 6 E1EDP10 1 2 1000 BW K151860 001 0.000000 20190624 20190625 0.000000 03 534 A11517
    313708998 0 7 E1EDP16 6 3 1000 4W20190809 20190809 6 A11517
    313709018 0 1 E1EDK09 0 1 1000 A11517 20190625190625 B A11517
    313709018 0 2 E1EDKA1 1 2 1000 LF FTL MFG A11517
    313734018 0 1 E1EDK09 0 1 1000 A11596 20190625190625 B A11596
    313734018 0 2 E1EDKA1 0 1 1000 LF FTL MFG A11596

  6. #16
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,679
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Power Query, What Am I missing here.

    ok I understand PO Number now but still I need representative example of source data which reflect structure and data type.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  7. #17
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,679
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Power Query, What Am I missing here.

    and why you extract 35 characters if you need only 6 ?
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  8. #18
    New Member
    Join Date
    Jul 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query, What Am I missing here.

    Quote Originally Posted by sandy666 View Post
    ok I understand PO Number now but still I need representative example of source data which reflect structure and data type.
    I already posted the source data

  9. #19
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,679
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Power Query, What Am I missing here.

    sure, my mistake

    maybe

    Code:
    // Query1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Type = Table.TransformColumnTypes(Source,{{"IDoc number", Int64.Type}, {"Counter", Int64.Type}, {"Segment No.", Int64.Type}, {"SAP segment name", type text}, {"No. higher segment", Int64.Type}, {"Hierarchy level", Int64.Type}, {"Data Filter Value fo", type any}, {"Length", Int64.Type}, {"Application data", type text}}),
        RC = Table.RemoveColumns(Type,{"Data Filter Value fo", "Hierarchy level", "No. higher segment", "Counter"}), Index = Table.AddIndexColumn(RC, "Index", 0, 1),
        Extract = Table.TransformColumns(Index, {{"Application data", each Text.BeforeDelimiter(_, " "), type text}}),
        PONumber = Table.FillDown(Table.AddColumn(Extract, "PO Number", each if [#"Segment No."] = 1 then Text.Trim (Text.Start([Application data],35)) else null),{"PO Number"})
    in
        PONumber
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  10. #20
    New Member
    Join Date
    Jul 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query, What Am I missing here.

    Quote Originally Posted by sandy666 View Post
    and why you extract 35 characters if you need only 6 ?
    that is the data that we are getting from outside party and they have 35 characters allocated for it. Does that influence what I am trying to do?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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