Inserting intermediate step issue - expanding rows

[Tashat]

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.

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"

 

[Tashat]

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!