# Power Query, What Am I missing here.

#### rp2019

##### New Member
Details:
Value=List
Key=PO Number

Last edited by a moderator:

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### sandy666

##### Well-known Member
Details:
Value=List
Key=PO Number

https://www.mrexcel.com/forum/image...rEAa56yJVLR0ssXiGKizPZbIGcWOI3JtsAwWTM2MYknne OLN7dXy3t z/N099NANwaYf8QA34 qRfSDRncDTz9PP79 nu7Zs7a2dvX73/8 PvGJT4D6E/OfthuPse7FvCEi6k6sn7uD5MOHR4ftHPWKnyws49fsayIiIiIiIqK wB7hXUzybTO8PG83/xcXF 0r4MMf/jCuv/56O0e9aqePMWofyz8RUXfiubM7bGU NPYIn3z8P CRx/9nOwc89od/ov 97p//c/z xOfwr377t/D//Zf/glf/92/gP/3t3 O6667D4T/8NP6HWz sl6ONk57czfDyUtbTViD83nvv4ezZs/jJT36i59PpNA4ePKhf07Uj fbAAw/Yuc688MILHVe0X/jCF/zG7z/ 4z/i85//vG4QU ...
I don't know this kind of code, it's any new language?

#### rp2019

##### New Member
I am new here, I copy pasted an image and it came that way.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed 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}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Data Filter Value fo", "Hierarchy level", "No. higher segment", "Counter"}),
Text.Trim (Text.Start([Application data],35))
else {[Index]-1}[#"PO Number"])
in

#### sandy666

##### Well-known Member
your code should be between [CODE]...[/CODE] tags like

Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed 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}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Data Filter Value fo", "Hierarchy level", "No. higher segment", "Counter"}), AddedIndex = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
#"Added Custom1" = Table.AddColumn(AddedIndex, "PO Number", each if [#"Segment No."] = 1 then Text.Trim (Text.Start([Application data],35)) else {[Index]-1}[#"PO Number"])
in
and post example of source[/edit] data associated with this code

Last edited:

#### rp2019

##### New Member
your code should be between [CODE]...[/CODE] tags like

Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed 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}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Data Filter Value fo", "Hierarchy level", "No. higher segment", "Counter"}), AddedIndex = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
#"Added Custom1" = Table.AddColumn(AddedIndex, "PO Number", each if [#"Segment No."] = 1 then Text.Trim (Text.Start([Application data],35)) else {[Index]-1}[#"PO Number"])
in
and post example data associated with this code

 Segment No. SAP segment name Length Application data Index PO Number 1 E1EDK09 1000 A11517 20190625190625 B -1 A11517 2 E1EDKA1 1000 LF FTL MFG 0 3 E1EDKA1 1000 YSCBW 7 1 4 E1EDK11 1000 YHT1 1 Customer Horizon Start Date: 06/25/19 Customer Horizon End Date: 12/22/19 2 5 E1EDK11 1000 YHT1 2 TMP 92 = 001 3 6 E1EDP10 1000 BW K151860 001 0.000000 20190624 20190625 0.000000 03 534 4 7 E1EDP16 1000 4W20190809 20190809 6 5 1 E1EDK09 1000 A11517 20190625190625 B 6 A11517
<colgroup><col width="105" style="width: 79pt; mso-width-source: userset; mso-width-alt: 3840;"> <col width="150" style="width: 113pt; mso-width-source: userset; mso-width-alt: 5485;"> <col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;"> <col width="568" style="width: 426pt; mso-width-source: userset; mso-width-alt: 20772;"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2157;"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;"> <tbody> </tbody>

#### Fluff

##### MrExcel MVP, Moderator
@rp2019
Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.
Also do not make spurious posts on other threads.

#### sandy666

##### Well-known Member
your source data has 9 columns but I see 6 only

Last edited:

#### rp2019

##### New Member
well, I have removed some columns in my steps

#### sandy666

##### Well-known Member
well, I have removed some columns in my steps
so post example of source data

#### rp2019

##### New Member
 number Counter Segment No. SAP segment name No. higher segment Hierarchy level Data Filter Value fo Length Application data 313708998 0 1 E1EDK09 0 1 1000 A11517 20190625190625 B 313708998 0 2 E1EDKA1 1 2 1000 LF FTL MFG 313708998 0 3 E1EDKA1 1 2 1000 YSCBW 7 313708998 0 4 E1EDK11 1 2 1000 YHT1 1 Customer Horizon Start Date: 06/25/19 Customer Horizon End Date: 12/22/19 313708998 0 5 E1EDK11 1 2 1000 YHT1 2 TMP 92 = 001 313708998 0 6 E1EDP10 1 2 1000 BW K151860 001 0.000000 20190624 20190625 0.000000 03 534 313708998 0 7 E1EDP16 6 3 1000 4W20190809 20190809 6 313709018 0 1 E1EDK09 0 1 1000 A11517 20190625190625 B 313709018 0 2 E1EDKA1 1 2 1000 LF FTL MFG
<colgroup><col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;"> <col width="134" style="width: 101pt; mso-width-source: userset; mso-width-alt: 4900;"> <col width="64" style="width: 48pt;" span="7"> <tbody> </tbody>

1,102,733
Messages
5,488,551
Members
407,646
Latest member
utl1095

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...