Data Modeling and Power Query

[WanyaPING12]

Hello PBI Forum, 

Good day! I need help on this excel data that needs to automate in the power BI. Can you help me give some tips and know how to do this in modeling and power query. I am new to power bi and I am so lost on what i need to do first or the best way to do this. I have provided here the sample excel data (step by step) and some of the excel formulas i used. Hoping that this forum can gave me idea on how to do this in power bi. Thank you in advance!

<!--Step 1 Raw Data, other columns is with formulas
data must be sorted first by ID and date process

Week	Date	Date Process	Group	ID	Status	Status 2	Category	Key Status & Status2	Owner (lookup)	Owner 2	Type	UnqCount	Duration (Hrs)	Wait time (hrs)	Wait Time (Hrs) 2	Duration less Wait
1	8/10/2025	########	A	01ID	ACTIVE	good to use	1D	ACTIVE|good to use	
0	BB	1	1.25	
0	1.25
1	8/10/2025	########	A	01ID	NONACTIVE	for endorse	1X	NONACTIVE|for endorse	
0	BB	1	4.63	1	1	3.63
1	8/10/2025	########	A	01ID	IDLE	waiting to response	1A	IDLE|waiting to response	EE	0	BB	1	2	0	2	0
1	8/10/2025	########	A	01ID	ACTIVE	good to use	1D	ACTIVE|good to use	
0	BB	1	4.42	1.25	1.25	3.17
1	8/10/2025	########	B	02ID	ACTIVE	good to use	1D	ACTIVE|good to use	
0	BB	2	1.75	0	0	1.75
1	8/10/2025	########	B	02ID	IDLE	waiting to response	1A	IDLE|waiting to response	EE	EE	AA	3	1	0.25	1	0
1	8/10/2025	########	B	02ID	IDLE	waiting to response	1A	IDLE|waiting to response	EE	EE	AA	3	1	8.5	1	0
1	8/11/2025	########	C	02ID	IDLE	waiting to response	1A	IDLE|waiting to response	EE	EE	AA	3	0.72	2.5	0.72	0
1	8/11/2025	########	C	03ID	IDLE	waiting to response	1A	IDLE|waiting to response	EE	0	BB	4	2	1.78	2	0
2	8/11/2025	########	A	03ID	WAITING	waiting to ack	1B	WAITING|waiting to ack	
0	BB	4	4.91	1.25	1.25	3.66
2	8/11/2025	########	A	03ID	WAITING	waiting to ack	1B	WAITING|waiting to ack	
0	BB	4	1.25	0	0	1.25
2	8/11/2025	########	A	03ID	IDLE	waiting to response	1A	IDLE|waiting to response	EE	0	BB	4	2	0.75	2	0
2	8/11/2025	########	B	03ID	ACTIVE	good to use	1D	ACTIVE|good to use	
0	BB	4	2.25	8.5	8.5	-6.25
2	8/12/2025	########	B	03ID	SETLING	setting 	1C	SETLING|setting 	
0	BB	4	1.5	0.25	0.25	1.25
2	8/12/2025	########	B	04ID	IDLE	waiting to response	1A	IDLE|waiting to response	EE	0	BB	5	3	1	3	0
2	8/12/2025	########	B	04ID	IDLE	waiting to response	1A	IDLE|waiting to response	EE	0	BB	5	0.93	0	0.93	0
2	8/13/2025	########	B	04ID	ACTIVE	good to use	1D	ACTIVE|good to use	
0	BB	5	2.46	1.32	1.32	1.14
2	8/13/2025	########	C	04ID	IDLE	waiting to response	1A	IDLE|waiting to response	EE	0	BB	5	1.5	1.79	1.5	0
2	8/13/2025	########	C	04ID	SETLING	setting 	1C	SETLING|setting 	
0	BB	5	2.75	10.5	10.5	-7.75
2	8/13/2025	########	C	04ID	IDLE	waiting to response	1A	IDLE|waiting to response	EE	EE	AA	6	0.57	0	0.57	0
Key Status & Status2 =   Status &"|"& Status 2

Owner 2 = IF(AND(ISNUMBER(SEARCH("IDLE",Key Status & Status 2)),PrevIDrow=NextIDRow),Owner2 row6,Owner(lookup))

Type = IF(OR(Owner2 ="", Owner2=0), "BB", "AA")

UniqCount = must place 1 on the prev row 1 then the formula next row is IF(AND(NextIDrow=PrevIDrow,NextTypeRow=PrevTypeRow),Row1,Row1+1)

Wait time Hrs 2 =  IF(ISNUMBER(SEARCH("IDLE",Key Status & Status2)),Duration Hrs,Wait time Hrs)

Duration Less Wait = Duration Hrs - Wait time Hrs



step 2: after adding columns with formulas, pivot table

Week	1	




Category	(Multiple Items)	exclude 1X category	













Values	


Group	UnqCount	Type	Sum of Duration (Hrs)	Sum of Duration less Wait	Sum of Wait Time (Hrs) 2
A	1	BB	7.67	4.42	3.25	
B	2	BB	1.75	1.75	0	
B	3	AA	2	0	2	
C	3	AA	0.72	0	0.72	
C	4	BB	2	0	2	
Grand Total	

14.14	6.17	7.97	


Step 3: after pivoting in this desired column output, pivot again to add column for the outlier checking if theres outlier data

Group	UnqCount	Type	Sum of Duration (Hrs)	Sum of Duration less Wait	Sum of Wait Time (Hrs) 2	Outlier
A	1	BB	7.67	4.42	3.25	ok
B	2	BB	1.75	1.75	0	ok
B	3	AA	2	0	2	ok
C	3	AA	0.72	0	0.72	ok
C	4	BB	2	0	2	ok
Outlier = IF(Type<(10/60),"outlier",IF(AND(Type="BB",Sum of Duration hrs>168),"outlier",IF(AND(Type="AA",Sum of Duration less wait>12),"outlier","ok")))


Step 4: then pivot again to gain this result of this 2 table

Outlier	(All)	
Outlier	(All)	

Type	BB	
Type	AA	








Row Labels	Average of Sum of Duration (Hrs)	Row Labels	Average of Sum of Duration less Wait	Average of Sum of Wait Time (Hrs) 2
A	7.67	
B	0	2	
B	1.75	
C	0	0.72	
C	2	
Grand Total	0	1.4	
Grand Total	3.8	
-->

[v-dineshya]

Hi @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

[MohamedFowzan1]

Hi @WanyaPING12 

- I would actually suggest sticking to the raw excel data. Import this in Powerquery and do the necessary cleaning and data type changes

- I noticed combining fields and simple if condition, all this can either be covered as calculared columns or measures else you could even build this within power query if needed upstream.

- Use matrix or table visuals to replicate Excel pivot tables using the measures or calculations you create.

The suggestion is to separate modeling and building of the actual visuals for better understanding and reusability.

[MFelix]

Hi @WanyaPING12 ,

First of all I believe that you need to split this into 2 different parts the data treament STEP 1 that will allow you to have the data in a format you can use and the rest of the steps that can be done using DAX calculations.

Can you please share a mockup data or sample of your XLSX file (the one you identify on  step 1). You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[WanyaPING12]

Thank you @MFelix. I've shared the mockup data thru private message.