Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jveffer
Frequent Visitor

Interesting problem - trying to get a value from the next row

Hi there!

I have a scheduled report that pulls in data from a CRM program that has to be cleaned so I can import into a task management platform.

The output from the CRM has one column that indicates :

 

Internal ID Project  Customer OpportunityTransaction NumberItem Opportunity Created Date

 

For each "Project" the Internal ID is the same- but there can be several Services to that client within the project.

Each time the "Internal ID" changes, it reflects a different Customer and Project, but the way it is output, there is no entry in the "Item" (or what we use for the different Services).

So, what I have done so far in Power Query is to replace values in Col B so that there are no blanks (replaced by null) and where there was an asterisk in the original table, I replaced with "CatName" as below. I also added a custom field called "Category" in Col G which is where I have issues.

 

PowerQuery1.jpg

 

When I manually edit each table in excel, I use this formula for Category

 

 

 

 

=IF(AND(B2<>"null",C3=C2),F3,F3)

 

 

 

 

Essentially for the "Category" in G2, we want the "Item" from the next row F3 or "ServiceA," and so on as below

 

PowerQuery2.jpg

 

Now the issue is with the code I am using.

 

Here is the code

 

 

 

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Jeffrey\Internal\Internal Projects - Internal Projects TEST\CAPACITY_RESOURCE_FORECAST\Wrike_Capacity_DataFiles\OpenOppsforJeffreyResults857TEST.xlsx"), null, true),
    OpenOppsforJeffreyResults857_Sheet = Source{[Item="OpenOppsforJeffreyResults857",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(OpenOppsforJeffreyResults857_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Internal ID", Int64.Type}, {"*", type text}, {"Customer", type text}, {"Opportunity", type text}, {"Transaction Number", Int64.Type}, {"Item", type text}, {"Opportunity Created Date", type date}, {"Expected Close Date", type date}, {"Probability", type number}, {"Projected Total", type number}, {"Weighted Total", type number}, {"Memo", type text}, {"Estimated Project Duration", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"*", "Project"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type),
    #"Replaced Value" = Table.ReplaceValue(#"Added Index"," ",null,Replacer.ReplaceValue,{"Project"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","*","CatName",Replacer.ReplaceText,{"Project"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Category", try each if Text.Contains("Replaced Value1" [Index] [Project] = "CatName") then #"Replaced Value1" {[Index] + 1} [#"[Item]"] else #"Replaced Value1" {[Index]} [#"[Item]"] otherwise Null.Type, type text),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Internal ID", "Project", "Customer", "Opportunity", "Transaction Number", "Item", "Category", "Opportunity Created Date", "Expected Close Date", "Probability", "Projected Total", "Weighted Total", "Memo", "Estimated Project Duration", "Index"}),
    Category = #"Reordered Columns"{25}[Category]
in
    Category

 

 

 

 

 

The issue is with the line

 

 

 

#"Added Custom" = Table.AddColumn(#"Replaced Value1", "Category", try each if Text.Contains("Replaced Value1" [Index] [Project] = "CatName") then #"Replaced Value1" {[Index] + 1} [#"[Item]"] else #"Replaced Value1" {[Index]} [#"[Item]"] otherwise Null.Type, type text),

 

 

 

 

While I don't get a Syntax error, I get this error

 

Expression.Error: We cannot apply field access to the type Text.
Details:
Value=Replaced Value1
Key=Index

 

I have no idea where to start as I thought that the structure was ok, but apparently not....

 

Any help is greatly appreciated!!

1 ACCEPTED SOLUTION

Hi @Syndicate_Admin  @jveffer 

 

If you don't convert Blank cells in [Project] to "null", you will not have this error.  But when you remove try...otherwise, you still might come across another error, if it happens to meet the condition at the last row, you won't have [Index]+1, so that's why you need error handling.

View solution in original post

10 REPLIES 10
Vera_33
Resident Rockstar
Resident Rockstar

Hi @jveffer 

 

Yes, you can add Category column and shift the Index like this

 

#"Added Custom" = Table.AddColumn(#"Replaced Value1", "Category", each 
if Text.Contains([Project],"CatName") then [Item] else #"Replaced Value1"[Item] {[Index] + 1}?  ),

 

but to your original code, you can do like this

 

Table.AddColumn(Table.AddColumn(#"Replaced Value1", "Category", each 
try if Text.Contains([Project],"CatName") then [Item] 
else #"Replaced Value1" {[Index]+1}[Item] otherwise null),

 

Hi there- 

For some reason, neither of these work in my context though in pasting maybe the formatting throws it off?

It gives the oppositite, but still an error?

jveffer_0-1625684071575.png

 

Hi @jveffer 

 

Oh yes, it is the opposite...I mistakenly changed the then...else...

BUT, what was the error? It becomes interesting now. Maybe the [Item]? I don't see the necessity to use [#"[Item]"], so changed to [Item].

How about you paste some sample original data, so I can replicate your error?

 

Table.AddColumn(Table.AddColumn(#"Replaced Value1", "Category", each 
try if Text.Contains([Project],"CatName") then #"Replaced Value1" {[Index]+1}[Item]
else [Item] otherwise null),

 

Thank you for the update! - I was able to get the first [Item] to show up in [Category] as below, but when I remove the error checking ("try" "otherwise") I get an error for the other fields in that column

Expression.Error: We cannot convert the value null to type Logical.

PowerQuery3.jpg

So perhaps I don't have to convert the blank cells in [Project] to "null" earlier on in the process?

Hi @Syndicate_Admin  @jveffer 

 

If you don't convert Blank cells in [Project] to "null", you will not have this error.  But when you remove try...otherwise, you still might come across another error, if it happens to meet the condition at the last row, you won't have [Index]+1, so that's why you need error handling.

For those who follow this post here is the solution code that works

let
    Source = Excel.Workbook(File.Contents("C:\Users\Jeffrey\Internal\InternalProjects - Internal Projects TEST\CAPACITY_RESOURCE_FORECAST\Capacity_DataFiles\OpenOppsforJeffreyResults.xlsx"), null, true),
    #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Internal ID", Int64.Type}, {"*", type text}, {"Customer", type text}, {"Opportunity", type text}, {"Transaction Number", Int64.Type}, {"Item", type text}, {"Opportunity Created Date", type date}, {"Expected Close Date", type date}, {"Probability", type number}, {"Projected Total", type number}, {"Weighted Total", type number}, {"Memo", type text}, {"Estimated Project Duration", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"*", "Project"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type),
    #"Replaced Value1" = Table.ReplaceValue(#"Added Index","*","CatName",Replacer.ReplaceText,{"Project"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Category", each try if Text.Contains([Project],"CatName") then #"Replaced Value1" {[Index]+1}[Item] else [Item] otherwise null ),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Internal ID", "Project", "Customer", "Opportunity", "Transaction Number", "Item", "Category", "Opportunity Created Date", "Expected Close Date", "Probability", "Projected Total", "Weighted Total", "Memo", "Estimated Project Duration", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"OpenOppsforJeffreyResults245_1", "Sheet", "false", "Estimated Project Duration", "Index"})
in
    #"Removed Columns"

YES ! This worked. Thank you so much for your help. Will mark the post as Solution

Thank you for the update! - I was able to get the first [Item] to show up in [Category] as below, but when I remove the error checking ("try" "otherwise") I get an error for the other fields in that column

Expression.Error: We cannot convert the value null to type Logical.

PowerQuery3.jpg

So perhaps I don't have to convert the blank cells in [Project] to "null" earlier on in the process?

Jakinta
Solution Sage
Solution Sage

What about "C:\Users\Jeffrey\Internal\Internal Projects - Internal Projects TEST\CAPACITY_RESOURCE_FORECAST\Wrike_Capacity_DataFiles\OpenOppsforJeffreyResults857TEST.xlsx"? 🙂

 

Anyway you can shift [Category] column as list by 1 and add it to existing table (Table.FromColumns). Remove old [Item] then.

Hi there - I am not sure I follow. Do you mean move [Category] down one row?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors