Power Query, What Am I missing here.

rp2019

New Member
Joined
Jul 9, 2019
Messages
11
Expression.Error: We cannot apply field access to the type List.
Details:
Value=List
Key=PO Number
 
Last edited by a moderator:
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]
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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]

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
 
Upvote 0
You calling PO Number itself
How should it look like according to you?

[TABLE="width: 862"]
<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>[TR]
[TD="width: 82, bgcolor: transparent"]number[/TD]
[TD="width: 64, bgcolor: transparent"]Counter[/TD]
[TD="width: 64, bgcolor: transparent"]Segment No.[/TD]
[TD="width: 124, bgcolor: transparent"]SAP segment name[/TD]
[TD="width: 96, bgcolor: transparent"]No. higher segment[/TD]
[TD="width: 64, bgcolor: transparent"]Hierarchy level[/TD]
[TD="width: 64, bgcolor: transparent"]Data Filter Value fo[/TD]
[TD="width: 64, bgcolor: transparent"]Length[/TD]
[TD="width: 462, bgcolor: transparent"]Application data[/TD]
[TD="width: 64, bgcolor: transparent"]PO_Number[/TD]
[/TR]
[TR]
[TD="width: 82, bgcolor: transparent"]313708998[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDK09[/TD]
[TD="width: 96, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]A11517 20190625190625 B[/TD]
[TD="bgcolor: transparent"]A11517[/TD]
[/TR]
[TR]
[TD="width: 82, bgcolor: transparent"]313708998[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDKA1[/TD]
[TD="width: 96, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]LF FTL MFG[/TD]
[TD="bgcolor: transparent"]A11517[/TD]
[/TR]
[TR]
[TD="width: 82, bgcolor: transparent"]313708998[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]3[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDKA1[/TD]
[TD="width: 96, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]YSCBW 7[/TD]
[TD="bgcolor: transparent"]A11517[/TD]
[/TR]
[TR]
[TD="width: 82, bgcolor: transparent"]313708998[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]4[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDK11[/TD]
[TD="width: 96, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]YHT1 1 Customer Horizon Start Date: 06/25/19 Customer Horizon End Date: 12/22/19[/TD]
[TD="bgcolor: transparent"]A11517[/TD]
[/TR]
[TR]
[TD="width: 82, bgcolor: transparent"]313708998[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]5[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDK11[/TD]
[TD="width: 96, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]YHT1 2 TMP 92 = 001[/TD]
[TD="bgcolor: transparent"]A11517[/TD]
[/TR]
[TR]
[TD="width: 82, bgcolor: transparent"]313708998[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]6[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDP10[/TD]
[TD="width: 96, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]BW K151860 001 0.000000 20190624 20190625 0.000000 03 534[/TD]
[TD="bgcolor: transparent"]A11517[/TD]
[/TR]
[TR]
[TD="width: 82, bgcolor: transparent"]313708998[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]7[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDP16[/TD]
[TD="width: 96, bgcolor: transparent"]6[/TD]
[TD="width: 64, bgcolor: transparent"]3[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]4W20190809 20190809 6[/TD]
[TD="bgcolor: transparent"]A11517[/TD]
[/TR]
[TR]
[TD="width: 82, bgcolor: transparent"]313709018[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDK09[/TD]
[TD="width: 96, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]A11517 20190625190625 B[/TD]
[TD="bgcolor: transparent"]A11517[/TD]
[/TR]
[TR]
[TD="width: 82, bgcolor: transparent"]313709018[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDKA1[/TD]
[TD="width: 96, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]LF FTL MFG[/TD]
[TD="bgcolor: transparent"]A11517[/TD]
[/TR]
[TR]
[TD="width: 82, bgcolor: transparent"]313734018[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDK09[/TD]
[TD="width: 96, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]A11596 20190625190625 B[/TD]
[TD="bgcolor: transparent"]A11596[/TD]
[/TR]
[TR]
[TD="width: 82, bgcolor: transparent"]313734018[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDKA1[/TD]
[TD="width: 96, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]LF FTL MFG[/TD]
[TD="bgcolor: transparent"]A11596[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
IGNORE MY PREV MSG

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

[TABLE="width: 958"]
<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>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: white"]number[/TD]
[TD="width: 64, bgcolor: transparent"]Counter[/TD]
[TD="width: 64, bgcolor: transparent"]Segment No.[/TD]
[TD="width: 124, bgcolor: transparent"]SAP segment name[/TD]
[TD="width: 96, bgcolor: transparent"]No. higher segment[/TD]
[TD="width: 64, bgcolor: transparent"]Hierarchy level[/TD]
[TD="width: 64, bgcolor: transparent"]Data Filter Value fo[/TD]
[TD="width: 64, bgcolor: transparent"]Length[/TD]
[TD="width: 462, bgcolor: transparent"]Application data[/TD]
[TD="width: 128, bgcolor: transparent, colspan: 2"]PO_Number[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: transparent"]313708998[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDK09[/TD]
[TD="width: 96, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]A11517 20190625190625 B[/TD]
[TD="bgcolor: transparent"]A11517[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: transparent"]313708998[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDKA1[/TD]
[TD="width: 96, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]LF FTL MFG[/TD]
[TD="bgcolor: transparent"]A11517[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: transparent"]313708998[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]3[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDKA1[/TD]
[TD="width: 96, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]YSCBW 7[/TD]
[TD="bgcolor: transparent"]A11517[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: transparent"]313708998[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]4[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDK11[/TD]
[TD="width: 96, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]YHT1 1 Customer Horizon Start Date: 06/25/19 Customer Horizon End Date: 12/22/19[/TD]
[TD="bgcolor: transparent"]A11517[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: transparent"]313708998[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]5[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDK11[/TD]
[TD="width: 96, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]YHT1 2 TMP 92 = 001[/TD]
[TD="bgcolor: transparent"]A11517[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: transparent"]313708998[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]6[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDP10[/TD]
[TD="width: 96, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]BW K151860 001 0.000000 20190624 20190625 0.000000 03 534[/TD]
[TD="bgcolor: transparent"]A11517[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: transparent"]313708998[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]7[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDP16[/TD]
[TD="width: 96, bgcolor: transparent"]6[/TD]
[TD="width: 64, bgcolor: transparent"]3[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]4W20190809 20190809 6[/TD]
[TD="bgcolor: transparent"]A11517[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: transparent"]313709018[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDK09[/TD]
[TD="width: 96, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]A11517 20190625190625 B[/TD]
[TD="bgcolor: transparent"]A11517[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: transparent"]313709018[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDKA1[/TD]
[TD="width: 96, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]LF FTL MFG[/TD]
[TD="bgcolor: transparent"]A11517[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: transparent"]313734018[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDK09[/TD]
[TD="width: 96, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]A11596 20190625190625 B[/TD]
[TD="bgcolor: transparent"]A11596[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: transparent"]313734018[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 124, bgcolor: transparent"]E1EDKA1[/TD]
[TD="width: 96, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]1000[/TD]
[TD="width: 462, bgcolor: transparent"]LF FTL MFG[/TD]
[TD="bgcolor: transparent"]A11596[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
ok I understand PO Number now but still I need representative example of source data which reflect structure and data type.
 
Upvote 0
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]
 
Upvote 0

Forum statistics

Threads
1,224,825
Messages
6,181,191
Members
453,021
Latest member
pingpong7117

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top