I did find a solution from https://www.mrexcel.com/board/threads/convert-2nd-column-values-to-rows-when-column-1-is-the-same-name.1063813/ that sort of worked, but not quite.

let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],

Grp = Table.Group(Source,
{"Superior Organization"},
{
{"Concat", each Text.Combine([Organization],",")},
{"Count", each List.Count([Organization]), Int64.Type}
},
GroupKind.Global),

Final = Table.RemoveColumns(
Table.SplitColumn(Grp,
"Concat",
Splitter.SplitTextByDelimiter(","),
List.Max(Grp[Count]),
"",
ExtraValues.Ignore),
{"Count"}
)

in
Final

It does seem to add multiple columns as needed, however the left-most column should only contain Group A, as it's the highest level for all organizations.

My first attachment was not even created properly. Please look at this one as well to get an idea of what I'm really attempting to achieve. Thanks again.

Hi Adam,

The second attachment isn't attached 🙂 Can you please try again? You need to click the yellow 'Start Upload' button to actually upload the file.

Cheers,

Mynda

Oh, sorry my apologies!

Hi, just wondering if anyone's had a chance to look into this. I hate to be a bother and waste time. I have a feeling I'm doing the only thing possible to achieve my goal.

Hi Adam,

That sure is confusing! I can't think of a more efficient process, but you might like to refer to this post: how to improve Power Query refresh times for ideas.

Mynda

Hi Adam,

I think a recursive function is the best approach.

Here is the GetOrg function:

What the function does: tries to find the Organization, that corresponds to the Superior Organization (by filtering the initial table). If an Organization is found (the filtered table is not empty - Filter[Organization]{0} triggers an error in this case), then the function calls itself by passing the newly found Organization to find its subsequent Org. The function calls itself recursively, until no more Organization is found.

Your main query should look like this:

Thank you both for your replies. Catalin I was able to get your code working in my file and it was pretty close, but it still wasn't able to to begin every row with Group A, etc. 

With that said, I rebuilt my file a little differently and it's going quite a bit faster. It helped I think that I broke my query up into two separate queries. Below is what the second half of my current query looks like. (not that it will make much sense)

let
Source = Workday,
#"Removed Columns" = Table.RemoveColumns(Source,{"Organization"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Superior Organization] <> "")),
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows"),
#"Merged Queries" = Table.NestedJoin(#"Removed Duplicates",{"Superior Organization"},Workday,{"Superior Organization"},"Workday",JoinKind.LeftOuter),
#"Expanded Workday" = Table.ExpandTableColumn(#"Merged Queries", "Workday", {"Organization"}, {"Workday.Organization"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Workday",{"Workday.Organization"},Workday,{"Superior Organization"},"Workday",JoinKind.LeftOuter),
#"Expanded Workday2" = Table.ExpandTableColumn(#"Merged Queries1", "Workday", {"Organization"}, {"Workday.Organization.1"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded Workday2",{"Workday.Organization.1"},Workday,{"Superior Organization"},"Workday",JoinKind.LeftOuter),
#"Expanded Workday1" = Table.ExpandTableColumn(#"Merged Queries2", "Workday", {"Organization"}, {"Workday.Organization.2"}),
#"Merged Queries3" = Table.NestedJoin(#"Expanded Workday1",{"Workday.Organization.2"},Workday,{"Superior Organization"},"Workday",JoinKind.LeftOuter),
#"Expanded Workday3" = Table.ExpandTableColumn(#"Merged Queries3", "Workday", {"Organization"}, {"Workday.Organization.3"}),
#"Merged Queries4" = Table.NestedJoin(#"Expanded Workday3",{"Workday.Organization.3"},Workday,{"Superior Organization"},"Workday",JoinKind.LeftOuter),
#"Expanded Workday4" = Table.ExpandTableColumn(#"Merged Queries4", "Workday", {"Organization"}, {"Workday.Organization.4"}),
#"Merged Queries5" = Table.NestedJoin(#"Expanded Workday4",{"Workday.Organization.4"},Workday,{"Superior Organization"},"Workday",JoinKind.LeftOuter),
#"Expanded Workday5" = Table.ExpandTableColumn(#"Merged Queries5", "Workday", {"Organization"}, {"Workday.Organization.5"}),
#"Merged Queries6" = Table.NestedJoin(#"Expanded Workday5",{"Workday.Organization.5"},Workday,{"Superior Organization"},"Workday",JoinKind.LeftOuter),
#"Expanded Workday6" = Table.ExpandTableColumn(#"Merged Queries6", "Workday", {"Organization"}, {"Workday.Organization.6"}),
#"Merged Queries7" = Table.NestedJoin(#"Expanded Workday6",{"Workday.Organization.6"},Workday,{"Superior Organization"},"Workday",JoinKind.LeftOuter),
#"Expanded Workday7" = Table.ExpandTableColumn(#"Merged Queries7", "Workday", {"Organization"}, {"Workday.Organization.7"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Workday7", each ([Superior Organization] = "Office of the President")),

*******

And there should be an image of how it was intended to look.

Thanks again for your time and effort.

Hi Adam,

You can use the same function that gets all dependents to build another recursive function that returns all precedents, then you can combine precedents and dependents into a single list, if that's what you need.

All you need to change is: instead of passing the Superior Organization and return the Organization, pass to the function the Organization and return the Superior Organization.

GetSupOrg should look like this:

The initial function call should also be adjusted:

#"Added Custom" = Table.AddColumn(Source, "Custom", each {[Organization]} & GetOrg(Source,[Organization])),

The precedents list should be reversed before combining it with dependents list. (List.Reverse)