Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
Hey Yuliana,
Yes, I managed to do it my self in the mean time, but your result is basically the same.
Thanks for helping!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |