• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Expanding a column with Multiple Instances across new columns|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Expanding a column with Multiple Instances across new columns|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryExpanding a column with Multiple In…
sp_PrintTopic sp_TopicIcon
Expanding a column with Multiple Instances across new columns
Avatar
Adam Smith
Member
Members
Level 0
Forum Posts: 45
Member Since:
December 18, 2018
sp_UserOfflineSmall Offline
1
March 12, 2020 - 1:01 am
sp_Permalink sp_Print

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.

Avatar
Adam Smith
Member
Members
Level 0
Forum Posts: 45
Member Since:
December 18, 2018
sp_UserOfflineSmall Offline
2
March 12, 2020 - 1:54 am
sp_Permalink sp_Print sp_EditHistory

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.

Avatar
Adam Smith
Member
Members
Level 0
Forum Posts: 45
Member Since:
December 18, 2018
sp_UserOfflineSmall Offline
3
March 12, 2020 - 2:12 am
sp_Permalink sp_Print

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.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
March 12, 2020 - 11:11 am
sp_Permalink sp_Print

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

Avatar
Adam Smith
Member
Members
Level 0
Forum Posts: 45
Member Since:
December 18, 2018
sp_UserOfflineSmall Offline
5
March 12, 2020 - 8:59 pm
sp_Permalink sp_Print

Oh, sorry my apologies!

Avatar
Adam Smith
Member
Members
Level 0
Forum Posts: 45
Member Since:
December 18, 2018
sp_UserOfflineSmall Offline
6
March 14, 2020 - 2:01 am
sp_Permalink sp_Print

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.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
7
March 14, 2020 - 9:43 am
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
March 14, 2020 - 5:48 pm
sp_Permalink sp_Print sp_EditHistory

Hi Adam,

I think a recursive function is the best approach.

Here is the GetOrg function:

(Tbl,Org)=>
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:

let
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"
Avatar
Adam Smith
Member
Members
Level 0
Forum Posts: 45
Member Since:
December 18, 2018
sp_UserOfflineSmall Offline
9
March 15, 2020 - 12:05 am
sp_Permalink sp_Print

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.

Blurred.pngImage Enlarger

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
10
March 15, 2020 - 6:00 pm
sp_Permalink sp_Print sp_EditHistory

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:

(Tbl,Org)=>
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)

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online:
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 215
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Anthony van Riessen
Erlinda Eloriaga
Abisola Ogundele
MARTYN STERRY
Rahim Lakhani
Ngoc Qui Nguyen
Clement Mansfield
Rose .
Bindu Menon
Baruch Zemer
Forum Stats:
Groups: 3
Forums: 24
Topics: 6352
Posts: 27779

 

Member Stats:
Guest Posters: 49
Members: 32319
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.