Power Query, What Am I missing here.

rp2019

New Member
Joined
Jul 9, 2019
Messages
11
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]
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.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,854
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?
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,854
try

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"}),
    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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,847
Messages
5,489,229
Members
407,682
Latest member
gmb2521

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...
Top