Could you explain what this code is supposed to do?
Code:if [#"Segment No."] = 1 then Text.Trim (Text.Start([Application data],35)) else [B][SIZE=3][COLOR=#ff0000]{[Index]-1}[#"PO Number"][/COLOR][/SIZE][/B]
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 |
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 | |||
ok I understand PO Number now but still I need representative example of source data which reflect structure and data type.
[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]
and why you extract 35 characters if you need only 6 ?