Power Query, What Am I missing here.

rp2019

New Member
Joined
Jul 9, 2019
Messages
11
sure, my mistake

maybe

Code:
[SIZE=1]// 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[/SIZE]
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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,364
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:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,364
try

Code:
[SIZE=1]// 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[/SIZE]
and from now try Text.Trim(Text.Start([Application data],35)) if you need string with 35 characters
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,261
Messages
5,467,638
Members
406,545
Latest member
puneet829

This Week's Hot Topics

Top