Get and Transform - changing/finding value if blank

[cmcreynolds]

Hello, I hope everyone is well. Long time, no post

I receive non-normalized data from our field staff who are in teams of 2. For our final analysis, we choose only the team lead (indicated in its own field/column as TL). However, on rare occasion, they leave their response blank and we need to consider the other team member's response.

The issue is there are 82 fields to consider (not my structure, btw). I could come up with an approach if there were only a handful of fields, but writing 82 IF statements would drive me quite bonkers.

I use Get and Transform to extract the data from various folders and I was hoping the solution could be used there. But I could only find a way to replace variables one column at a time.

Here's a short version of how the data is set up.

SiteCode	TeamLead	Q1	Q2	Q3	Q4
033	TL	1	2	3	5
033	NL	1	2	3	5
151	TL	99	2	4	4
151	NL	1	2	3	3
222	TL	4	99	1	1
222	NL	2	4	2	3
167	TL	99	0	99	99
167	NL	1	3	2	2

<tbody>
</tbody>

Basically, whenever a TL has a '99' we look at the NL's response. Example: For Q1, site 151, we would want a '1' in the final data set

Again, I have 82 fields to apply this to.

(I looked at 'Group By' and it seemed daunting, I was hoping there was functionality I was overlooking - either in PowerQuery or PowerPivot)

Thank you!
Clifton

 

[sandy666]

there will be any number, different or always the same (in yellow fields) or they will be blank?

However, on rare occasion, they leave their response blank and we need to consider the other team member's response.

what max number is proper (I assume not 99 )

 

[sandy666]

try this

SiteCode	TeamLead	Q1	Q2	Q3	Q4
033	TL	1​	2​	3​	5​
151	TL	1​	2​	4​	4​
222	TL	4​	4​	1​	1​
167	TL	1​	0​	2​	2​

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SiteCode", type text}}),
    #"Filled Up" = Table.FillUp(#"Changed Type",{"Q1", "Q2", "Q3", "Q4"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([TeamLead] = "TL"))
in
    #"Filtered Rows"[/SIZE]

the assumption is that 99 means blank as you wrote in the first post

 

[cmcreynolds]

Yes, 99 is blank. Thank you.

try this

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]SiteCode[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]TeamLead[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Q1[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Q2[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Q3[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Q4[/COLOR]
033	TL	1​	2​	3​	5​
151	TL	1​	2​	4​	4​
222	TL	4​	4​	1​	1​
167	TL	1​	0​	2​	2​

<tbody>
</tbody>

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SiteCode", type text}}),
    #"Filled Up" = Table.FillUp(#"Changed Type",{"Q1", "Q2", "Q3", "Q4"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([TeamLead] = "TL"))
in
    #"Filtered Rows"[/SIZE]

the assumption is that 99 means blank as you wrote in the first post

 

[sandy666]

so post#12 is a solution

 

[cmcreynolds]

Almost, there were some other assumptions -

Assumption 1 - that there were exactly two people on the team, TL and NL. I can adjust the data so that I take one NL, but what if there were no NL
Assumption 2 - What if the TL and the NL both code blank? Will Table.FillUp use the next non-empty cell? Even if it's from another site visit?

 

[sandy666]

post a link to representative source data in example excel file, with expected result

 

[cmcreynolds]

I can work around assumption 1, it's just the issue of two blank in a row. Now, I code them as 99 but in order to use Table.FillUp, the cells have to be blank/empty, correct?

The expected result for both being blank would be just a 99.