Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have next data in Excel:
If I read it into Power Bi, I would like to get something like:
Based on condition like:
If Hum. >= 80 and Temp. <= 2 ---> Next row fill it with nulls
Is it possible to apply conditions while loading data? Should I load the source data and, after that, apply conditions to the whole table? Is it possible to appply conditions to the whole table at once rather than column by column?
Solved! Go to Solution.
Hi , @Anonymous
Try steps as below:
Enter the interface of "edit queries"/"transform data"
1.Create index column
2. right click "Insert step after" to insert step "filter"
= Table.SelectRows(#"Added Index",each if [Index]=1 then true else not(#"Added Index"{[Index]-2}[#"Hum."]>=80 and #"Added Index"{[Index]-2}[#"Temp."]<=2))
It will show as below:
3. add step "AddNullrow" :
= List.Generate( () =>
[RowNumber = Table.RowCount(#"Added Index"), TableResult = FilterTable, Counter = 1],
each [Counter] <= [RowNumber]+1,
each [
TableResult = if let c = [Counter] in Table.RowCount(Table.SelectRows(FilterTable, each [Index] = c)) = 0
then Table.InsertRows([TableResult],[Counter]-1, {[#"Hum." = null, #"Temp." = null, #"Vel." = null, Index = [Counter]]})
else [TableResult],
Counter = [Counter] + 1,
RowNumber = [RowNumber]
],
each [TableResult]
){Table.RowCount(#"Added Index")}
It will show as below:
Here is a demo.
PS. Replacing with null will consume a lot of performance, and if your data source is excel, method will not shorten the refresh time
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi , @Anonymous
Try steps as below:
Enter the interface of "edit queries"/"transform data"
1.Create index column
2. right click "Insert step after" to insert step "filter"
= Table.SelectRows(#"Added Index",each if [Index]=1 then true else not(#"Added Index"{[Index]-2}[#"Hum."]>=80 and #"Added Index"{[Index]-2}[#"Temp."]<=2))
It will show as below:
3. add step "AddNullrow" :
= List.Generate( () =>
[RowNumber = Table.RowCount(#"Added Index"), TableResult = FilterTable, Counter = 1],
each [Counter] <= [RowNumber]+1,
each [
TableResult = if let c = [Counter] in Table.RowCount(Table.SelectRows(FilterTable, each [Index] = c)) = 0
then Table.InsertRows([TableResult],[Counter]-1, {[#"Hum." = null, #"Temp." = null, #"Vel." = null, Index = [Counter]]})
else [TableResult],
Counter = [Counter] + 1,
RowNumber = [RowNumber]
],
each [TableResult]
){Table.RowCount(#"Added Index")}
It will show as below:
Here is a demo.
PS. Replacing with null will consume a lot of performance, and if your data source is excel, method will not shorten the refresh time
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-easonf-msft,
Thank you for your worked answer. Nonetheless, I am quite newbie in DAX programming language and I would be grateful if you could ask some comments on step 3 when you add the "AddNullrow".
At the same time, when you say that this approach it is going to consume a lot of performance, is it because I want to convert that row to, specifically, nulls? Would it be the same consumption if that row is convert to 0, for example?
Refer, if these can help
https://community.powerbi.com/t5/Desktop/Filter-row-with-Power-query-m/td-p/571779
https://docs.microsoft.com/en-us/powerquery-m/table-selectrows
@amitchandak Thanks for links. Nonetheless, I want to convert to null next row based on condition checked on previous one.
I have seen that it is possible to filter rows but based on its own values. How can apply the same approach but using values from previous row? Is that possible?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |