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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
animeshsingh737
Frequent Visitor

checking for a condition and creating a table consisting a single row

Case 1 table -

DateNameCostPart
21-11-2023historical205
22-11-2023historical306
23-11-2023historical407
24-11-2023historical508
25-11-2023historical609
26-11-2023historical70

10

 

27-11-2023historical8011
28-11-2023historical9012
 predicted  
 lower  
 upper  

 

Case 2 Table : 

DateNameCostPart
21-11-2023historical205
22-11-2023historical306
23-11-2023historical407
24-11-2023historical508
25-11-2023historical609
26-11-2023historical7010
27-11-2023historical8011
28-11-2023historical9012
29-11-2023forecast10013
29-11-2023lower9812.5
29-11-2023upper102

13.5

30-11-2023predicted110

14

30-11-2023lower108

13.5

30-11-2023upper112

14.5

 

Case 3 table

 

DateNameCostPart
29-11-2023predicted10010
29-11-2023lower988
29-11-2023upper10212
30-11-2023predicted11012
30-11-2023lower10810
30-11-2023upper11214

I am new to Power BI  but I need to do this report. Above is a table and I want to create a table with a only row selected from this table. 

The conditions are:

  1. If there is no cost data for the predicted, lower, and upper names and only the historical name has cost data, then I want to select the last "historical" name row and create a table with that single row. (refer to first case table)
  2. If there are the predicted, lower, and upper names and corresponding cost data are also there then I want to select the last row with the "historical" name and create a table with that single row.
  3. If there are not any single rows of "historical", then, in that case, I want a table with the first row with the predicted name.

One point to be noted is that in the first case, the date value is also not there for the predicted, upper, and lower names.

I am using power query to get the data with post request so it would be nice if I could use this calculation after retrieving the data in the power query itself.

 

Expected outcome:

In the first case : 

28-11-2023historical90

12

 

In the second case : 

28-11-2023historical9012

 

In the third case :

29-11-2023predicted10010
1 ACCEPTED SOLUTION

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc3dCoMwDIbhe+mxjib9sV6LeCDimCBU1LHbX5NsY1ByEgg8fO8wGIQWoEWLzjTmsZ5XPtZ52sqDtpxgxqYg1JAjFAU5DXlCnSCvoUAoCQoaioR6QVFDHSGwojpNJVYgKmmqZ4Wi+j91z8cyT+fFKUauRlt+LQetJF65hZo8950JWOSRj3FWaQG3fI2+LbBJHfrFgGOezPgG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, Cost = _t, Part = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Name", type text}, {"Cost", Int64.Type}, {"Part", type number}}),
    Historicals = Table.SelectRows(#"Changed Type", each ([Name] = "historical")),
    Forecast = Table.SelectRows(#"Changed Type", each ([Name] = "forecast"))
in
    if Table.RowCount(Historicals) >0 then Table.LastN(Historicals,1) else Table.FirstN(Forecast,1)

View solution in original post

7 REPLIES 7
lbendlin
Super User
Super User

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Thanks for pointing out the mistake. I am new to this forum but I updated the question and added the sample data in a table. Hope this will provide more clarity

 

for both scenarios you can apply these rules

 

- filter out all rows without values

- filter out all rows where the name is "upper" or "lower"

- keep bottom 1 row.

This is just a sample data. In the second case, there can be multiple dates with forecast, lower and upper. I want the first forecast for that. But in the first case, if upper, lower, and forecast are empty in a row then there will be no further rows.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

I updated the question. I added a new condition for more clarity.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc3dCoMwDIbhe+mxjib9sV6LeCDimCBU1LHbX5NsY1ByEgg8fO8wGIQWoEWLzjTmsZ5XPtZ52sqDtpxgxqYg1JAjFAU5DXlCnSCvoUAoCQoaioR6QVFDHSGwojpNJVYgKmmqZ4Wi+j91z8cyT+fFKUauRlt+LQetJF65hZo8950JWOSRj3FWaQG3fI2+LbBJHfrFgGOezPgG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, Cost = _t, Part = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Name", type text}, {"Cost", Int64.Type}, {"Part", type number}}),
    Historicals = Table.SelectRows(#"Changed Type", each ([Name] = "historical")),
    Forecast = Table.SelectRows(#"Changed Type", each ([Name] = "forecast"))
in
    if Table.RowCount(Historicals) >0 then Table.LastN(Historicals,1) else Table.FirstN(Forecast,1)

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.