# Power Query, What Am I missing here.



## rp2019 (Jul 9, 2019)

Expression.Error: We cannot apply field access to the type List.
Details:
    Value=List
    Key=PO Number


----------



## sandy666 (Jul 9, 2019)

rp2019 said:


> Expression.Error: We cannot apply field access to the type List.
> Details:
> Value=List
> Key=PO Number
> ...



I don't know this kind of code, it's any new language?


----------



## rp2019 (Jul 9, 2019)

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"}),
    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
    #"Added Custom1"


----------



## sandy666 (Jul 9, 2019)

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


```
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
    #"Added Custom1"
```

and post example *[edit]*of source*[/edit]* data associated with this code


----------



## rp2019 (Jul 9, 2019)

sandy666 said:


> your code should be between [CODE]...[/CODE] tags like
> 
> 
> ```
> ...





Segment No.SAP segment nameLengthApplication dataIndexPO Number1E1EDK091000A11517                              20190625190625                                                                                                                                                                                                                                                                                                    B-1A115172E1EDKA11000LF  FTL  MFG0 3E1EDKA11000YSCBW 71 4E1EDK111000YHT1   1 Customer Horizon Start Date:  06/25/19                                  Customer Horizon End Date: 12/22/192 5E1EDK111000YHT1   2 TMP 92 = 0013 6E1EDP101000BW  K151860                                                                001                                                                                           0.000000                       20190624          20190625                              0.000000                                                                                            03                                                                                                                                                                        5344 7E1EDP1610004W20190809    20190809   65 1E1EDK091000A11517                              20190625190625                                                                                                                                                                                                                                                                                                    B6A11517
 
<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 (Jul 9, 2019)

@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 (Jul 9, 2019)

rp2019 said:


> .



your source data has 9 columns but I see 6 only


----------



## rp2019 (Jul 9, 2019)

well, I have removed some columns in my steps


----------



## sandy666 (Jul 9, 2019)

rp2019 said:


> well, I have removed some columns in my steps



so post example of *source* data


----------



## rp2019 (Jul 9, 2019)

numberCounterSegment No.SAP segment nameNo. higher segmentHierarchy levelData Filter Value foLengthApplication data31370899801E1EDK09011000A11517                              20190625190625                                                                                                                                                                                                                                                                                                    B31370899802E1EDKA1121000LF FTL  MFG31370899803E1EDKA1121000YSCBW 731370899804E1EDK11121000YHT1   1 Customer Horizon Start  Date: 06/25/19                                  Customer Horizon End Date: 12/22/1931370899805E1EDK11121000YHT1   2 TMP 92 = 00131370899806E1EDP10121000BW K151860                                                                001                                                                                           0.000000                       20190624          20190625                              0.000000                                                                                            03                                                                                                                                                                        53431370899807E1EDP166310004W20190809    20190809  631370901801E1EDK09011000A11517                              20190625190625                                                                                                                                                                                                                                                                                                    B31370901802E1EDKA1121000LF 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>


----------



## rp2019 (Jul 9, 2019)

Expression.Error: We cannot apply field access to the type List.
Details:
    Value=List
    Key=PO Number


----------



## sandy666 (Jul 9, 2019)

Could you explain what this code is supposed to do?


```
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]
```


----------



## rp2019 (Jul 9, 2019)

sandy666 said:


> Could you explain what this code is supposed to do?
> 
> 
> ```
> ...



I am looking at the column "Segment_No". if it is 1 then, the PO_Number=Application_data field is trimmed 1-35 , else PO_Number = PO_Number from the previous record


----------



## sandy666 (Jul 9, 2019)

You calling PO Number itself 
How should it look like according to you?


----------



## rp2019 (Jul 10, 2019)

sandy666 said:


> You calling PO Number itself
> How should it look like according to you?




numberCounterSegment  No.SAP  segment nameNo.  higher segmentHierarchy  levelData  Filter Value foLengthApplication  dataPO_Number31370899801E1EDK0901 1000A11517  20190625190625 BA1151731370899802E1EDKA112 1000LF  FTL MFGA1151731370899803E1EDKA112 1000YSCBW  7A1151731370899804E1EDK1112 1000YHT1  1 Customer Horizon Start Date: 06/25/19 Customer Horizon End Date: 12/22/19A1151731370899805E1EDK1112 1000YHT1  2 TMP 92 = 001A1151731370899806E1EDP1012 1000BW  K151860 001 0.000000 20190624 20190625 0.000000 03 534A1151731370899807E1EDP1663 10004W20190809  20190809 6A1151731370901801E1EDK0901 1000A11517  20190625190625 BA1151731370901802E1EDKA112 1000LF  FTL MFGA1151731373401801E1EDK0901 1000A11596  20190625190625 BA1159631373401801E1EDKA101 1000LF  FTL MFGA11596
 
<colgroup><col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;">
 
<col width="64" style="width: 48pt;" span="2">
 
<col width="124" style="width: 93pt; mso-width-source: userset; mso-width-alt: 4534;">
 
<col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;">
 
<col width="64" style="width: 48pt;" span="3">
 
<col width="462" style="width: 347pt; mso-width-source: userset; mso-width-alt: 16896;">
 
<col width="64" style="width: 48pt;">
 
<tbody>
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
 

</tbody>


----------



## rp2019 (Jul 10, 2019)

IGNORE MY PREV MSG



sandy666 said:


> You calling PO Number itself
> How should it look like according to you?




numberCounterSegment  No.SAP  segment nameNo.  higher segmentHierarchy  levelData  Filter Value foLengthApplication  dataPO_Number31370899801E1EDK0901 1000A11517  20190625190625 BA1151731370899802E1EDKA112 1000LF  FTL MFGA1151731370899803E1EDKA112 1000YSCBW  7A1151731370899804E1EDK1112 1000YHT1  1 Customer Horizon Start Date: 06/25/19 Customer Horizon End Date: 12/22/19A1151731370899805E1EDK1112 1000YHT1  2 TMP 92 = 001A1151731370899806E1EDP1012 1000BW  K151860 001 0.000000 20190624 20190625 0.000000 03 534A1151731370899807E1EDP1663 10004W20190809  20190809 6A1151731370901801E1EDK0901 1000A11517  20190625190625 BA1151731370901802E1EDKA112 1000LF  FTL MFGA1151731373401801E1EDK0901 1000A11596  20190625190625 BA1159631373401802E1EDKA101 1000LF  FTL MFGA11596
 
<colgroup><col width="64" style="width: 48pt;">
 
<col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;">
 
<col width="64" style="width: 48pt;" span="2">
 
<col width="124" style="width: 93pt; mso-width-source: userset; mso-width-alt: 4534;">
 
<col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;">
 
<col width="64" style="width: 48pt;" span="3">
 
<col width="462" style="width: 347pt; mso-width-source: userset; mso-width-alt: 16896;">
 
<col width="64" style="width: 48pt;" span="2">
 
<tbody>
  
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
  
  
 
 
  
  
  
  
  
  
  
  
  
  
  
  
 

</tbody>


----------



## sandy666 (Jul 10, 2019)

ok I understand PO Number now but still I need representative example of source data which reflect structure and data type.


----------



## sandy666 (Jul 10, 2019)

and why you extract 35 characters if you need only 6 ?


----------



## rp2019 (Jul 11, 2019)

sandy666 said:


> ok I understand PO Number now but still I need representative example of source data which reflect structure and data type.



I already posted the source data


----------



## sandy666 (Jul 11, 2019)

sure, my mistake

maybe


```
[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]
```


----------



## rp2019 (Jul 11, 2019)

sandy666 said:


> and why you extract 35 characters if you need only 6 ?



that is the data that we are getting from outside party and they have 35 characters allocated for it. Does that influence what I am trying to do?


----------



## rp2019 (Jul 9, 2019)

Expression.Error: We cannot apply field access to the type List.
Details:
    Value=List
    Key=PO Number


----------



## rp2019 (Jul 11, 2019)

sandy666 said:


> sure, my mistake
> 
> maybe
> 
> ...



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.


----------



## sandy666 (Jul 11, 2019)

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?


----------



## sandy666 (Jul 11, 2019)

try 


```
[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


----------

