Power Query, What Am I missing here.
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,575
    Post Thanks / Like
    Mentioned
    20 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
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #23
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,575
    Post Thanks / Like
    Mentioned
    20 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
    impossible things we do on the spot. for miracles you need to wait for a while

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
  •