Forum Discussion
Conditionally duplicate text in one or two columns across 3 columns, maybe
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)))
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
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?
how do you know "quiet" is a parent company?
or what's the difference between these 20% data and that 10% data?
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 helphope 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)))