Conditionally duplicate text in one or two columns across 3 columns, maybe

[Anonymous]

Thank you for any help or advice, I'll describe the scenario and goal in 2 different examples in case one example is easier to solve for than the other. Bottom line is we get some disorganized data sourced and we need to adjust and clean it it for our reports. I will explain the 4 types of data between these 3 columns, and what the desired action / output is.

I'm sure there are ways to automate most of these tasks in Power Query, I just don't know how yet!

In example 1, below, I will explain the 4 types of data between these 3 columns and what the desired action / output is:

Example and goal 1:

I have 3 columns with different company names in them, this is because our parent and child companies have hierarchies we need to track. 50% of the columns have 1 company name in column A that needs to be duplicated across the other 2 columns and this is if the names in column B and C are the parent company name or a 0 for no value. So it would be 

• Column A: Child company name
• Column B: Parent company name
• Column C: 0 (or Parent company name again)

The goal is to make the child company name all 3 columns, for our pivot report templates.

Then for 20% of the data there are 2 child company names in column A and B, with our parent company name in C, a hierarchy of children company names. For these I need to take Column B name and move/duplicate it over to Column C, and then I need to duplicate Column A name to also be in Column B.

Another 20% of columns have child company name in column A and a child company name in column B that is also in column C, for those I want Column A company name duplicated to Column B, while Column C remains the same. 

The last 10% of the time there are 3 different hierarchical children company names and we want to keep those as they are. 

That's example 1. Example 2 is what the data looks like before it is combined:

We have 2 sheets, one sheet with sales data and one sheet with the company names in hierarchy format as they relate to our sales agents. In excel I add 3 empty columns for the child company hierarchies, and I use a Vlookup of the agent ID number to automatically fill each of the 3 columns with the raw and not desired hierarchy data. I do this prior to taking it into power query for data transformation, becuase power query seems to have a complicated process if you wanted to duplicate that action.

If there's a more efficient way to produce the desired outcome of example 1 in this format, then please let me know! I felt example 1 was easier to manage or work with but I wanted to include this part for full disclosure! Thank you for any advice you have for automating example 1 tasks in power query.

[wdx223_Daniel]

for example 1, you can add a new step like this

NewStep=#table(Table.ColumnNames(PreviousStepName),Table.ToList(PreviousStepName,each List.Transform(List.Positions(_),(x)=>List.Last(List.RemoveItems(List.FirstN(_,x+1),{"",null,0,"0"}),null))))

for example 2, if the agent id is unique in hierarchy data, you can try this.

let

source1=sales data,

source2=hierarchy data,

custom1=Table.ExpandRecordColumn(Table.AddColumn(source1,"n",each source2{[agentid=[agentid]]}?),"n",{"A","B","C"})

in

custom1

[Anonymous]

You are a genius!!! Thank you so much. In example 1 I wasn't wholly clear but your solution is for a good chunk of the data yet the other 20% needs a different command it seems, for more context here's an exerpt from the middle of my request for example 1:
"Then for 20% of the data there are 2 child company names in column A and B, with our parent company name in C, a hierarchy of children company names. For these I need to take Column B name and move/duplicate it over to Column C, and then I need to duplicate Column A name to also be in Column B."

A demo of the example problem is below and is company name "help me" in column A with a middle company Help me2 in column B, with the parent company "quiet" in column C. 
Problem: Column A| Column B|Column C          >   Needed Solution: Column A| Column B| Column C

               help me   | Help me2 |  quiet               >                                help me  | help me   | Help me2

Is there a command that can identify these differences and perform this action without affecting the other data that doesn't have the same problem? 

[wdx223_Daniel]

how do you know "quiet" is a parent company?

or what's the difference between these 20% data and that 10% data?

[Anonymous]

Thanks for your patience and continued assistance. The easiest answer is the difference between this 20% and the 10% that doesn't need to be adjusted, so I'll start there.

I work with the parent company, and we don't need our name included in the pivot table I'm supposed to feed the data into. If I boil it down, there's 1 company name at the top of the hierarchy that we don't need to include in our 3 columns, the parent company name "quiet" or the value 0 which are in column C.

In the 10% of good data that doesn't need to be touched, the parent company name isn't listed because there are 3 child companies under us that fill the 3 columns of company hierarchy.

At first I was only thinking of tackling all of this data in one sheet, but I imagine now I can easily filter the 10% perfect companies out of the list and we then only deal with the 50% and 20% data.

In 20% of data I want to automate cleaning, there's only 2 child company names so our parent company name is in column C. The goal is to move the first level child company from column B to column C and to duplicate the second level child company in column A to column B.

I hope this gives you an idea of what step commands you can provide that could help

[wdx223_Daniel · Accepted solution]

hope this can help

NewStep=#table(Table.ColumnNames(PreviousStepName),Table.ToList(PreviousStepName,each if List.Contains({"",null,0,"0","quiet"},_{1}) then List.Repeat({_{0}},3) else if List.Contains({"",null,0,"0","quiet"},_{2}) then {_{0}}&List.FirstN(_,2)))