Forum Discussion
Data Modeling and Power Query
Hi WanyaPING12 ,
Please try below things.
1. Data Modeling in Power BI: Create Relationships, If you have lookup tables (Owner Lookup), connect them via ID or Owner.
2. Avoid Calculated Columns for Large Data.
3. Use DAX Measures instead of calculated columns where possible to reduce memory usage.
4. Avoid row-by-row logic in Power Query for large datasets.
5. Consider aggregating data before loading into power bi if possible.
6. Use Dataflows if your data source is very large and shared across reports.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Community SupportHi WanyaPING12 ,
Thank you for reaching out to the Microsoft Community Forum.
Please follow below steps.
1. Data clean:
Open Power BI Desktop, Click Home --> Get Data --> Excel and select your file. Go to Power query editor.
2. Replace excel formulas with Power query and DAX.
Owner 2 column:
if Text.Contains([Key Status & Status2], "IDLE") then [Owner] else [Owner Lookup]
Type column:
if [Owner2] = "" or [Owner2] = "0" then "BB" else "AA"
UnqCount column:
In Power Query, add a Index column. Create a conditional columnn comparing [ID] and [Type] with the previous row's values.
Wait Time Hrs 2:
if Text.Contains([Key Status & Status2], "IDLE") then [Duration Hrs] else [Wait time Hrs]
Duration Less wait:
[Duration Hrs] - [Wait time Hrs]
3. Instead of pivot tables use below DAX measures.
SumDuration = SUM(Data[Duration Hrs])
SumDurationLessWait = SUM(Data[Duration less Wait])
SumWaitTime = SUM(Data[Wait time Hrs 2])
UnqCount = DISTINCTCOUNT(Data[ID])
4. For Outlier Detection use below Measure.
OutlierFlag =
SWITCH(TRUE(),
AVERAGE(Data[Type]) < (10/60), "Outlier",
MAX(Data[Type]) = "BB" && [SumDuration] > 168, "Outlier",
MAX(Data[Type]) = "AA" && [SumDurationLessWait] > 12, "Outlier",
"OK"
)
5. Use Matrix visual instead of Pivot tables. Drag Group, Type fields in Rows and UnqCount, SumDuration, SumDurationLessWait, SumWaitTime in Values and add OutlierFlag as a conditional formatting indicator.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
- v-dineshya
Hi WanyaPING12 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
- v-dineshya
Hi @WanyaPING12 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Thank you for this step-by-step flow v-dineshya. I followed this kind of step, but I processed the Uniq Count on the Excel data before loading and processing other columns in the Power Query or table. I do have a large set of data, and the Power BI exceeds its limit, especially on row-level checking.
- v-dineshya
Hi WanyaPING12 ,
Please try below things.
1. Data Modeling in Power BI: Create Relationships, If you have lookup tables (Owner Lookup), connect them via ID or Owner.
2. Avoid Calculated Columns for Large Data.
3. Use DAX Measures instead of calculated columns where possible to reduce memory usage.
4. Avoid row-by-row logic in Power Query for large datasets.
5. Consider aggregating data before loading into power bi if possible.
6. Use Dataflows if your data source is very large and shared across reports.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
- v-dineshya
Hi @WanyaPING12 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
