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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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?
 
Upvote 0
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_Number
31370899801E1EDK0901 1000A11517 20190625190625 BA11517
31370899802E1EDKA112 1000LF FTL MFGA11517
31370899803E1EDKA112 1000YSCBW 7A11517
31370899804E1EDK1112 1000YHT1 1 Customer Horizon Start Date: 06/25/19 Customer Horizon End Date: 12/22/19A11517
31370899805E1EDK1112 1000YHT1 2 TMP 92 = 001A11517
31370899806E1EDP1012 1000BW K151860 001 0.000000 20190624 20190625 0.000000 03 534A11517
31370899807E1EDP1663 10004W20190809 20190809 6A11517
31370901801E1EDK0901 1000A11517 20190625190625 BA11517
31370901802E1EDKA112 1000LF FTL MFGA11517
31373401801E1EDK0901 1000A11596 20190625190625 BA11596
31373401801E1EDKA101 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>
 
Upvote 0
IGNORE MY PREV MSG

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_Number
31370899801E1EDK0901 1000A11517 20190625190625 BA11517
31370899802E1EDKA112 1000LF FTL MFGA11517
31370899803E1EDKA112 1000YSCBW 7A11517
31370899804E1EDK1112 1000YHT1 1 Customer Horizon Start Date: 06/25/19 Customer Horizon End Date: 12/22/19A11517
31370899805E1EDK1112 1000YHT1 2 TMP 92 = 001A11517
31370899806E1EDP1012 1000BW K151860 001 0.000000 20190624 20190625 0.000000 03 534A11517
31370899807E1EDP1663 10004W20190809 20190809 6A11517
31370901801E1EDK0901 1000A11517 20190625190625 BA11517
31370901802E1EDKA112 1000LF FTL MFGA11517
31373401801E1EDK0901 1000A11596 20190625190625 BA11596
31373401802E1EDKA101 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>
 
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,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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