Create table from 6 steps in the same excel file

[wolfy_]

Hi guy's,

I'm trying to find a better way to perform what I'm doing at the moment.

I what to transform visualize data like this, from a excel file that is generated automatically by a system

Project ID	Steep	Date started	Date Completed
1	Contact	01/06/2020	01/06/2020
1	Assesment	05/06/2020	05/06/2020
2	Contact	15/06/2020	15/06/2020
2	Assesment	20/06/2020	20/06/2020
3	Contact	01/07/2020	01/07/2020
3	Assesment	01/07/2020	02/07/2020
1	Teste	01/07/2020	01/07/2020
3	Teste	02/07/2020	02/07/2020
1	Contract	02/07/2020	02/07/2020
1	Implement	03/07/2020	03/07/2020
2	Teste	04/07/2020	04/07/2020
3	Contract	05/07/2020	05/07/2020
2	Contract	08/07/2020	08/07/2020
3	Implement	10/07/2020	10/07/2020
1	KPI OK	14/07/2020	14/07/2020
3	KPI OK	15/07/2020	15/07/2020
2	Implement
2	KPI OK

To show in PowerBI in a table

Project ID	Contact	Assesment	Teste	Contract	Implement	KPI OK
1	01/06/2020	05/06/2020	01/07/2020	02/07/2020	03/07/2020	14/07/2020
2	15/06/2020	20/06/2020	04/07/2020	08/07/2020
3	01/07/2020	02/07/2020	02/07/2020	05/07/2020	10/07/2020	15/07/2020

What I'm doing at the moment is importing the same file 6 times and filter "Steep" in each import by Contact, Assessment, etc.

Then I connect each table by a relation for "Project ID" and construct a table as shown before.

I believe there is a better way to do it, so I do not have to import the same file 6 times, filter it and do Relations between the 6 imports.

Do you have any suggestion?

[MFelix · Accepted solution]

Hi @wolfy_ ,

Believe that the best option would be to use a matrix visualization and using the column Steep to make you calculation and filtering in measure or something.

However if you only have one file what you need to do is some advance pivoting.

Check the code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc8xD4IwEAXgv0KYSegdVFiNE2HQwY0wGNNN0Nj+/2AR8F0uUQcSrv3y7rXrUkqz9HAfw+UaXn+GcrPL2bCRQ5+95d575wc3ztaitcKySCWUpCWmsgELQ7SF6lph10pK0VVYFja+6+x8cH9kbo6/58WWz6XmD9oMj5tbaxZoC2EZ15foSlUT11ukVkUirZHWKhWbkgELw/qq9tQkxzbeYVXSVT8Qi5IuituT+VtvtojluJ8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, Steep = _t, #"Date started" = _t, #"Date Completed" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", Int64.Type}, {"Steep", type text}, {"Date started", type date}, {"Date Completed", type date}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Date Completed"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Project ID", "Steep"}, "Attribute", "Value"),
    #"Removed Duplicates" = Table.Distinct(#"Unpivoted Columns"),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Steep", "Project ID"}, {{"Count", each _, type table [Project ID=nullable number, Steep=nullable text, Attribute=text, Value=date, Index=number]}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "Count", "Count - Copy"),
    #"Aggregated Count" = Table.AggregateTableColumn(#"Duplicated Column", "Count", {{"Index", List.Min, "Min of Count.Index"}}),
    #"Expanded Count - Copy" = Table.ExpandTableColumn(#"Aggregated Count", "Count - Copy", {"Attribute", "Index", "Value"}, {"Count - Copy.Attribute", "Count - Copy.Index", "Count - Copy.Value"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Count - Copy", "Custom", each [#"Count - Copy.Index"]-[Min of Count.Index]+1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Min of Count.Index", "Count - Copy.Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Steep]), "Steep", "Count - Copy.Value"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Count - Copy.Attribute", "Custom"})
in
    #"Removed Columns2"

Also added the PBIX file