Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Split Power Query code into Applied Steps / Steps not showing

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?

 

 

 

 

  • Hi Anonymous,

     

    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

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee

    Hi Anonymous,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hey Yuliana,


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

       

      Thanks for helping!