cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wolfy_
Helper I
Helper I

Create table from 6 steps in the same excel file

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 IDSteepDate startedDate Completed
1Contact01/06/202001/06/2020
1Assesment05/06/202005/06/2020
2Contact15/06/202015/06/2020
2Assesment20/06/202020/06/2020
3Contact01/07/202001/07/2020
3Assesment01/07/202002/07/2020
1Teste01/07/202001/07/2020
3Teste02/07/202002/07/2020
1Contract02/07/202002/07/2020
1Implement03/07/202003/07/2020
2Teste04/07/202004/07/2020
3Contract05/07/202005/07/2020
2Contract08/07/202008/07/2020
3Implement10/07/202010/07/2020
1KPI OK14/07/202014/07/2020
3KPI OK15/07/202015/07/2020
2Implement  
2KPI OK  

 

To show in PowerBI in a table

Project IDContactAssesmentTesteContractImplementKPI OK
101/06/202005/06/202001/07/202002/07/202003/07/202014/07/2020
215/06/202020/06/202004/07/202008/07/2020  
301/07/202002/07/202002/07/202005/07/202010/07/202015/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?

1 ACCEPTED SOLUTION
MFelix
Super User III
Super User III

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User III
Super User III

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.