Hello,
I have trouble with Power Query steps that I have made for one fact table. It is very very slow and I don't know how to fix it. Here is the M code of the table :
let
Sprint = Table.SelectRows(Table.SelectColumns(DimSprint,{"SprintName", "Date of beginning ", "Date of end"}), each ([#"Date of beginning "] <> null)),
Source = AzureStorage.DataLake("https://snprsparkdashboardsa.dfs.core.windows.net/dashboardcontainer/powerbi-source/sonar"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Table Column1",{"Source.Name"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns1",{{"date", type text}, {"project", type text}, {"overall status", type text}, {"coverage", type number}, {"bugs", Int64.Type}, {"vulnerabilities", Int64.Type}, {"code_smells", Int64.Type}, {"complexity", Int64.Type}, {"duplicated_lines_density", type number}, {"tech debt", Int64.Type}, {"reliability_rating", Int64.Type}, {"security_rating", Int64.Type}, {"maintainability_rating", Int64.Type}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type3", "date", Splitter.SplitTextByPositions({0, 4}, true), {"date.1", "date.2"}),
#"Removed Columns2" = Table.RemoveColumns(#"Split Column by Position",{"date.2"}),
#"Split Column by Positions" = Table.SplitColumn(#"Removed Columns2", "date.1", Splitter.SplitTextByPositions({0, 2, 4}), {"date.1.1", "date.1.2", "date.1.3"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Split Column by Positions",{"date.1.3", "date.1.1", "date.1.2", "project", "overall status", "coverage", "bugs", "vulnerabilities", "code_smells", "complexity", "duplicated_lines_density", "tech debt", "reliability_rating", "security_rating", "maintainability_rating"}),
#"Reordered Columns" = Table.ReorderColumns(#"Reordered Columns1",{"date.1.2", "date.1.1", "project", "overall status", "coverage", "bugs", "vulnerabilities", "code_smells", "complexity", "duplicated_lines_density", "tech debt", "reliability_rating", "security_rating", "maintainability_rating"}),
#"Merged Columns" = Table.CombineColumns(#"Reordered Columns",{"date.1.3", "date.1.2", "date.1.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
#"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Merged", "Date"}}),
#"Divided Column" = Table.TransformColumns(#"Renamed Columns", {{"coverage", each _ / 100, type number}}),
#"Added Custom" = Table.AddColumn(#"Divided Column", "Sprint", each Sprint),
#"Expanded Sprint1" = Table.ExpandTableColumn(#"Added Custom", "Sprint", {"SprintName", "Date of beginning ", "Date of end"}, {"SprintName", "Date of beginning ", "Date of end"}),
InRange = Table.AddColumn(#"Expanded Sprint1", "InRange", each if [Date] >= [#"Date of beginning "] and [Date] <= [Date of end] then "yes" else "no"),
#"Changed Type" = Table.TransformColumnTypes(InRange,{{"SprintName", type text}, {"Date of beginning ", type date}, {"Date of end", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([InRange] = "yes")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Date of beginning ", "Date of end", "InRange"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Date", "project", "overall status", "coverage", "complexity", "duplicated_lines_density", "SprintName"}, "Attribute", "Value"),
#"Renamed Columns2" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Code Quality"}})
in
#"Renamed Columns2"
The table DimSprint has 26 rows and 3 columns (excel file on sharepoint).
The table Sonar has 2088 rows and 13 columns (csv file on azure datalake).
I did theses transformation because in my table Sonar I need to know, based on the date column, what is the correspondent sprint. Please see attached the 2 tables (samples).
https://www.dropbox.com/s/hipvg05duq23t3g/DimSprint%20and%20Sonar.xlsx?dl=0
Thank you
Best regards
