Page 3 of 3 FirstFirst 123
Results 21 to 23 of 23

Thread: Power Query, What Am I missing here.

  1. #21
    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
    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
    that works. thanks for that. But i want to understand what i was doing wrong. Are we using the index column at all in your code? And why do we trim the Application Data column. I need it for further operations.

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

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

    Text.Start cut Application data to 35 characters (A11517 20190625190625 B) then you are trying get from these string PO Number which looks like this: A11517
    I think you lost one step like extract proper string. I used Extract before delimiter (space) because I don't know how PO Number should look like.

    edit:
    So this is not your own M-code but copied from somewhere?
    Last edited by sandy666; Jul 11th, 2019 at 08:01 AM.
    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
    I don't use vba in any form!

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

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

    try

    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"}),
        Extract = Table.AddColumn(RC, "Text Before Delimiter", each Text.BeforeDelimiter([Application data], " "), type text),
        PO = Table.FillDown(Table.AddColumn(Extract, "PO number", each if [#"Segment No."] = 1 then [Text Before Delimiter] else null),{"PO number"}),
        ROC = Table.SelectColumns(PO,{"IDoc number", "Segment No.", "SAP segment name", "Length", "Application data", "PO number"})
    in
        ROC
    and from now try Text.Trim(Text.Start([Application data],35)) if you need string with 35 characters
    Last edited by sandy666; Jul 11th, 2019 at 08:25 AM.
    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
    I don't use vba in any form!

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
  •