Log In
Register
Home
December 18, 2018
Offline
Apologies in advance for the terrible subject line. Please allow me to explain in more detail.
I have two columns: Supervisory Organization, Organization.
I'm essentially trying to create a set of columns containing our company's organizational hierarchy. The problem is, in Column A, there are organizations that also belong in Column B.
Group A is the top organization in the company, and group B would be the next level below, however, there are other additional organizations below group B.
I've managed to solve the problem using multiple Merges within the query, however with my actual data set this is very very slow. I'm wondering if there's some other way to Pivot the columns or Transpose or something that would make this a lot faster.
Please see attached workbook, and thanks so much for your help.
December 18, 2018
Offline
I did find a solution from https://www.mrexcel.com/board/.....e.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.
December 18, 2018
Offline
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.
July 16, 2010
Offline
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
December 18, 2018
Offline
Oh, sorry my apologies!
December 18, 2018
Offline
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.
July 16, 2010
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Adam,
I think a recursive function is the best approach.
Here is the GetOrg function:
let
Source = Tbl,
Filter=Table.SelectRows(Source, each _[Superior Organization] = Org),
OrgName=Filter[Organization]{0},
Result=try {OrgName} & GetOrg(Tbl,OrgName) otherwise {}
in
Result
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:
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each {[Superior Organization]} & GetOrg(Source,[Superior Organization])),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text})
in
#"Extracted Values"
December 18, 2018
Offline
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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:
let
Source = Tbl,
Filter=Table.SelectRows(Source, each _[Organization] = Org),
OrgName=Filter[Superior Organization]{0},
Result=try {OrgName} & GetOrg(Tbl,OrgName) otherwise {}
in
Result
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)
1 Guest(s)
