Split Power Query code into Applied Steps / Steps not showing

[donaldo]

Hi all,

I've edited a table in advanced editor. In the process Power BI consolidated a lot of actions into one step. I would like to retain the option of having all steps shown individually. Only the second part is shown as individual steps.

let
    Idea = let
    Source = Sql.Database("Server", "database"),
    dbo_Idea = Source{[Schema="dbo",Item="Idea"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_Idea,{{"IL0", type date}, {"LastEditDate", type date}, {"ModifiedDate", type date}, {"CreatedDate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ITT Link", each "website" & [ID]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Code","Description", "CurrentState", "FutureState", "RiskAssesment", "CancelReason", "ApprovedBy", "Transferred", "TransferredDescription", "LogBook", "LogBook_Backup", "LastEditDate", "LastEditUser", "_RowVersion", "Group", "Supplier", "Comments", "TCO", "VO", "Investment", "Category", "FinancialImpact", "ActivityTotalSum", "NotificationMailDaySpan", "CostExpanded", "Responsible2", "CommentsIssues", "RecommendedActions", "SupplierList", "ReasonForChange", "PriceIncreaseApproval", "ReasonForPriceChange", "FPPPilot", "CDEName", "AribaId", "Robotics","Priority", "ModifiedDate", "IsArchived"}),
    #"Expanded ImplementationLevel(IL5)" = Table.ExpandRecordColumn(#"Removed Columns", "ImplementationLevel(IL5)", {"ExpectedProgressClosed"}, {"ImplementationLevel(IL5).ExpectedProgressClosed"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded ImplementationLevel(IL5)",{{"ImplementationLevel(IL5).ExpectedProgressClosed", type date}}),
    StartDate=Date.StartOfYear(DateTime.LocalNow())-1
in
    #"Changed Type1",
    Custom1 = Idea,
    #"Filtered Rows" = Table.SelectRows(Custom1, each [#"ImplementationLevel(IL5).ExpectedProgressClosed"] > Date.AddYears(Date.StartOfYear(Date.From(DateTime.LocalNow())),-1)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Responsible.1", each [#"Person(Responsible)"]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Responsible.1"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([IL0] = null)),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows1", "Active Implementation Level", each if [ActiveLevel] = 5 then [IL5] else if [ActiveLevel] = 4 then [IL4] else if [ActiveLevel] = 3 then [IL3] else if [ActiveLevel] = 2 then [IL2] else if [ActiveLevel] = 1 then [IL1] else if [ActiveLevel] = 0 then [ID] else null),
    #"Invoked Custom Function" = Table.AddColumn(#"Added Conditional Column", "Category Function", each GetHierarchyCategoryNameDelimiter([CategoryId])),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Invoked Custom Function", "Category Function", Splitter.SplitTextByDelimiter("*", QuoteStyle.Csv), {"Category Function.1", "Category Function.2", "Category Function.3", "Category Function.4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Category Function.1", type text}, {"Category Function.2", type text}, {"Category Function.3", type text}, {"Category Function.4", type text}})
in
    #"Changed Type"

Can you split the code before the first in into steps again?

[v-yulgu-msft · Accepted solution]

Hi @donaldo,

Is this what you want?

let
   
    Source = Sql.Database("Server", "database"),
    dbo_Idea = Source{[Schema="dbo",Item="Idea"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_Idea,{{"IL0", type date}, {"LastEditDate", type date}, {"ModifiedDate", type date}, {"CreatedDate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ITT Link", each "website" & [ID]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Code","Description", "CurrentState", "FutureState", "RiskAssesment", "CancelReason", "ApprovedBy", "Transferred", "TransferredDescription", "LogBook", "LogBook_Backup", "LastEditDate", "LastEditUser", "_RowVersion", "Group", "Supplier", "Comments", "TCO", "VO", "Investment", "Category", "FinancialImpact", "ActivityTotalSum", "NotificationMailDaySpan", "CostExpanded", "Responsible2", "CommentsIssues", "RecommendedActions", "SupplierList", "ReasonForChange", "PriceIncreaseApproval", "ReasonForPriceChange", "FPPPilot", "CDEName", "AribaId", "Robotics","Priority", "ModifiedDate", "IsArchived"}),
    #"Expanded ImplementationLevel(IL5)" = Table.ExpandRecordColumn(#"Removed Columns", "ImplementationLevel(IL5)", {"ExpectedProgressClosed"}, {"ImplementationLevel(IL5).ExpectedProgressClosed"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded ImplementationLevel(IL5)",{{"ImplementationLevel(IL5).ExpectedProgressClosed", type date}}),
                       StartDate=Date.StartOfYear(DateTime.LocalNow())-1,

    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [#"ImplementationLevel(IL5).ExpectedProgressClosed"] > Date.AddYears(Date.StartOfYear(Date.From(DateTime.LocalNow())),-1)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Responsible.1", each [#"Person(Responsible)"]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Responsible.1"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([IL0] = null)),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows1", "Active Implementation Level", each if [ActiveLevel] = 5 then [IL5] else if [ActiveLevel] = 4 then [IL4] else if [ActiveLevel] = 3 then [IL3] else if [ActiveLevel] = 2 then [IL2] else if [ActiveLevel] = 1 then [IL1] else if [ActiveLevel] = 0 then [ID] else null),
    #"Invoked Custom Function" = Table.AddColumn(#"Added Conditional Column", "Category Function", each GetHierarchyCategoryNameDelimiter([CategoryId])),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Invoked Custom Function", "Category Function", Splitter.SplitTextByDelimiter("*", QuoteStyle.Csv), {"Category Function.1", "Category Function.2", "Category Function.3", "Category Function.4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Category Function.1", type text}, {"Category Function.2", type text}, {"Category Function.3", type text}, {"Category Function.4", type text}})
in
    #"Changed Type"

Regards,

Yuliana Gu

[donaldo]

Hey Yuliana,

Yes, I managed to do it my self in the mean time, but your result is basically the same.

Thanks for helping!