Inserting intermediate step issue - expanding rows

T

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
135
Office Version
  1. 365
Platform
  1. Windows
Hello all

I'm very new to PowerBI, but am slowly getting to learn it. I have created queries with multiple steps - mostly related to expanding fields / columns with multiple entries to create additional rows - which is what I want to do. Further down the applied steps, I have then grouped rows and counted the number of unique IDs which are the result of the expanded columns - so I can perform some other calculations. My issue is that I have missed out one field and forgot to expand it further up the query. However, when I try to add this step in - I get the "inserting an intermediate step may affect subsequent steps" warning. When I insert anyway, and I go to the bottom of the query, the column I tried to expand isn't expanded properly. Is there a way of inserting this step without having to deleted and recreate all the subsequent steps from that point on? I've managed to find the advanced editor to paste the code below - none of which I've written directly - just used the functions in powerbi. I would want to expand another field at the stage where the rest of the fields are expanded.

Many thanks in advance for anyone's help.


Power Query: 
let
    Source = SharePoint.Tables("https://officenationalstatistics.sharepoint.com/sites/ouanen", [Implementation="2.0", ViewMode="All"]),
    #"28ee3f3b-f9ba-43bb-bcb0-b42742786db9" = Source{[Id="28ee3f3b-f9ba-43bb-bcb0-b42742786db9"]}[Items],
    #"Expanded Associated Activity ID" = Table.ExpandTableColumn(#"28ee3f3b-f9ba-43bb-bcb0-b42742786db9", "Associated Activity ID", {"lookupId", "lookupValue"}, {"Associated Activity ID.lookupId", "Associated Activity ID.lookupValue"}),
    #"Expanded Assigned Team(s)" = Table.ExpandListColumn(#"Expanded Associated Activity ID", "Assigned Team(s)"),
    #"Expanded O&E Lead" = Table.ExpandListColumn(#"Expanded Assigned Team(s)", "O&E Lead"),
    #"Expanded O&E Lead1" = Table.ExpandRecordColumn(#"Expanded O&E Lead", "O&E Lead", {"title"}, {"O&E Lead.title"}),
    #"Expanded Workstream(s)" = Table.ExpandListColumn(#"Expanded O&E Lead1", "Workstream(s)"),
    #"Expanded Linked HS IDs" = Table.ExpandTableColumn(#"Expanded Workstream(s)", "Linked HS IDs", {"lookupId", "lookupValue"}, {"Linked HS IDs.lookupId", "Linked HS IDs.lookupValue"}),
    #"Expanded LA / Organisation Name" = Table.ExpandTableColumn(#"Expanded Linked HS IDs", "LA / Organisation Name", {"lookupId", "lookupValue"}, {"LA / Organisation Name.lookupId", "LA / Organisation Name.lookupValue"}),
    #"Expanded V&E Organisation(s)" = Table.ExpandTableColumn(#"Expanded LA / Organisation Name", "V&E Organisation(s)", {"lookupId", "lookupValue"}, {"V&E Organisation(s).lookupId", "V&E Organisation(s).lookupValue"}),
    #"Expanded LA Attendees / Invitees" = Table.ExpandTableColumn(#"Expanded V&E Organisation(s)", "LA Attendees / Invitees", {"lookupId", "lookupValue"}, {"LA Attendees / Invitees.lookupId", "LA Attendees / Invitees.lookupValue"}),
    #"Expanded Linked AFO" = Table.ExpandTableColumn(#"Expanded LA Attendees / Invitees", "Linked AFO", {"lookupId", "lookupValue"}, {"Linked AFO.lookupId", "Linked AFO.lookupValue"}),
    #"Expanded Modified By" = Table.ExpandListColumn(#"Expanded Linked AFO", "Modified By"),
    #"Expanded Modified By1" = Table.ExpandRecordColumn(#"Expanded Modified By", "Modified By", {"title"}, {"Modified By.title"}),
    #"Expanded Created By" = Table.ExpandListColumn(#"Expanded Modified By1", "Created By"),
    #"Expanded Created By1" = Table.ExpandRecordColumn(#"Expanded Created By", "Created By", {"title"}, {"Created By.title"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Created By1",{{"Start Date", type date}, {"End Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Count ID", each _, type table [ID=nullable number, Activity ID=text, Start Date=nullable date, End Date=nullable date, Format of Activity=nullable text, Title=text, Associated Activity ID.lookupId=any, Associated Activity ID.lookupValue=any, Activity Description=nullable text, Aim of Activity=nullable text, Activity Status=nullable text, #"Assigned Team(s)"=text, #"Linked V&E Activity Code"=any, #"Joint Activity?"=nullable text, Inbox=nullable text, CCS Ref Code=nullable text, #"O&E Lead.title"=text, ONS collaborators=nullable text, Bucket=nullable text, #"Workstream(s)"=text, Linked HS IDs.lookupId=any, Linked HS IDs.lookupValue=any, #"LA / Organisation Name.lookupId"=number, #"LA / Organisation Name.lookupValue"=text, #"V&E Organisation(s).lookupId"=nullable number, #"V&E Organisation(s).lookupValue"=nullable text, #"LA Attendees / Invitees.lookupId"=nullable number, #"LA Attendees / Invitees.lookupValue"=nullable text, #"V&E Attendees / Invitees"=table, Attendee List SharePoint Link=nullable text, #"Number of attendees / recipients"=nullable number, Activity Outcome Summary=nullable text, Positive Stakeholder Quotes=nullable text, Gov Delivery Total Opens=nullable number, Gov Delivery Unique Opens=nullable number, #"Gov Delivery Unique Open Rate %"=nullable number, Gov Delivery Total Clicks=nullable number, #"Gov Delivery Unique Click Rate %"=nullable number, Gov Delivery Unsubscribed=nullable number, Gov Delivery Recipient Report=nullable text, Response=nullable text, #"How successful was this activity?"=nullable text, #"Highlight or Issue?"=nullable text, #"Details of Highlight / Issue"=nullable text, SharePoint Link 1=nullable text, SharePoint Link 2=nullable text, SharePoint Link 3=nullable text, Linked AFO.lookupId=nullable number, Linked AFO.lookupValue=nullable text, Retention label=text, Modified By.title=text, #"LA / Organisation Name: ID"=text, #"LA Attendees / Invitees: ID"=nullable text, Created By.title=text, Created=nullable datetime, Week Start Date=text, Week Start=text, Compliance Asset Id=nullable text, Color Tag=nullable text, Content Type=text, Modified=nullable datetime, Version=nullable text, Attachments=nullable text, Edit=any, Type=any, Item Child Count=text, Folder Child Count=text, Label setting=text, Retention label Applied=text, Label applied by=text, Item is a Record=any, App Created By=any, App Modified By=any]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ID Count", each Table.RowCount([Count ID])),
    #"Expanded Count ID" = Table.ExpandTableColumn(#"Added Custom", "Count ID", {"ID", "Activity ID", "Start Date", "End Date", "Format of Activity", "Title", "Associated Activity ID.lookupId", "Associated Activity ID.lookupValue", "Activity Description", "Aim of Activity", "Activity Status", "Assigned Team(s)", "Linked V&E Activity Code", "Joint Activity?", "Inbox", "CCS Ref Code", "O&E Lead.title", "ONS collaborators", "Bucket", "Workstream(s)", "Linked HS IDs.lookupId", "Linked HS IDs.lookupValue", "LA / Organisation Name.lookupId", "LA / Organisation Name.lookupValue", "V&E Organisation(s).lookupId", "V&E Organisation(s).lookupValue", "LA Attendees / Invitees.lookupId", "LA Attendees / Invitees.lookupValue", "V&E Attendees / Invitees", "Attendee List SharePoint Link", "Number of attendees / recipients", "Activity Outcome Summary", "Positive Stakeholder Quotes", "Gov Delivery Total Opens", "Gov Delivery Unique Opens", "Gov Delivery Unique Open Rate %", "Gov Delivery Total Clicks", "Gov Delivery Unique Click Rate %", "Gov Delivery Unsubscribed", "Gov Delivery Recipient Report", "Response", "How successful was this activity?", "Highlight or Issue?", "Details of Highlight / Issue", "SharePoint Link 1", "SharePoint Link 2", "SharePoint Link 3", "Linked AFO.lookupId", "Linked AFO.lookupValue", "Retention label", "Modified By.title", "LA / Organisation Name: ID", "LA Attendees / Invitees: ID", "Created By.title", "Created", "Week Start Date", "Week Start", "Compliance Asset Id", "Color Tag", "Content Type", "Modified", "Version", "Attachments", "Edit", "Type", "Item Child Count", "Folder Child Count", "Label setting", "Retention label Applied", "Label applied by", "Item is a Record", "App Created By", "App Modified By"}, {"ID.1", "Activity ID", "Start Date", "End Date", "Format of Activity", "Title", "Associated Activity ID.lookupId", "Associated Activity ID.lookupValue", "Activity Description", "Aim of Activity", "Activity Status", "Assigned Team(s)", "Linked V&E Activity Code", "Joint Activity?", "Inbox", "CCS Ref Code", "O&E Lead.title", "ONS collaborators", "Bucket", "Workstream(s)", "Linked HS IDs.lookupId", "Linked HS IDs.lookupValue", "LA / Organisation Name.lookupId", "LA / Organisation Name.lookupValue", "V&E Organisation(s).lookupId", "V&E Organisation(s).lookupValue", "LA Attendees / Invitees.lookupId", "LA Attendees / Invitees.lookupValue", "V&E Attendees / Invitees", "Attendee List SharePoint Link", "Number of attendees / recipients", "Activity Outcome Summary", "Positive Stakeholder Quotes", "Gov Delivery Total Opens", "Gov Delivery Unique Opens", "Gov Delivery Unique Open Rate %", "Gov Delivery Total Clicks", "Gov Delivery Unique Click Rate %", "Gov Delivery Unsubscribed", "Gov Delivery Recipient Report", "Response", "How successful was this activity?", "Highlight or Issue?", "Details of Highlight / Issue", "SharePoint Link 1", "SharePoint Link 2", "SharePoint Link 3", "Linked AFO.lookupId", "Linked AFO.lookupValue", "Retention label", "Modified By.title", "LA / Organisation Name: ID", "LA Attendees / Invitees: ID", "Created By.title", "Created", "Week Start Date", "Week Start", "Compliance Asset Id", "Color Tag", "Content Type", "Modified", "Version", "Attachments", "Edit", "Type", "Item Child Count", "Folder Child Count", "Label setting", "Retention label Applied", "Label applied by", "Item is a Record", "App Created By", "App Modified By"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Count ID", "Average Attendees / Recipients", each [#"Number of attendees / recipients"]/[ID Count]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Average Attendees / Recipients", type number}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type1", "ID and How successful", each Text.Combine({Text.From([ID], "en-GB"), [#"How successful was this activity?"]}, " "), type text),
    #"Inserted Merged Column1" = Table.AddColumn(#"Inserted Merged Column", "ID and Activity Status", each Text.Combine({Text.From([ID], "en-GB"), [Activity Status]}, " "), type text),
    #"Inserted Merged Column2" = Table.AddColumn(#"Inserted Merged Column1", "ID and Format of Activity", each Text.Combine({Text.From([ID], "en-GB"), [Format of Activity]}, " "), type text)
in
    #"Inserted Merged Column2"
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Hey all

I solved the problem myself and thought I better share what I did for any other PowerBI newbies out there...

  1. I identified which stage of the query that the field I expanded was no longer showing the correct results, by clicking through each step and checking the results in the "transform data" mode
  2. I noticed it was from the "Grouped rows" stage.
  3. I opened the advanced editor and noticed that the new columns produced from expanding the rows weren't in there
  4. I edited the query by following the syntax in the query and entered in the new column headings, and deleting the incorrect field name
  5. I re-expanded the count ID field and deleted the existing "Expanded Count ID" step
It seemed to fix it. I'm fully aware that my query above is probably not in the best order and that it is very likely that there are things that could be done to streamline etc. but I've only been using PowerBi for a week, so it's a steep learning curve!
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

T
  • Question
PowerBI - Linked sources - splicer not working - expanded rows
Replies
0
Views
245
Tashat
T
M
  • Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
G
  • Question
A comparison of a set range of columns in to another on the same sheet to highlight rows with differences...
Replies
1
Views
1K
goldbeje
G
D
  • Question
Automation of process using Excel Macro, FTP site, Sharepoint, and Windows Task Scheduler
Replies
1
Views
10K
ddalexander1
D

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top