Power BI Help

[kinitundi]

Hi Group,

I am new user here, i want some help in power bi where i am posting the table and output which i want.

Here days count is manual entry, if we can get from target date column similar result will be great. or you can use days count.

Thanks in Advance

Project	Project 2	Target Date	Days Count
KB	Feat 1	11-06-2023	30
KB	Feat 2	12-07-2023	60
KB	Feat 3	13-06-2023	30
KB	Feat 4	14-07-2023	60
KB	Feat 5	15-09-2023	180
APPMMM	Feat 1	11-06-2023	30
APPMMM	Feat 2	12-07-2023	60
APPMMM	Feat 3	13-06-2023	30
APPMMM	Feat 4	14-07-2023	60
APPMMM	Feat 5	15-09-2023	180
Integration	Feat 1	11-06-2023	30
Integration	Feat 2	12-07-2023	60
Integration	Feat 3	13-06-2023	30
Integration	Feat 4	14-07-2023	60
Integration	Feat 5	15-09-2023	180

Output

Project	30	60	90	180
KB	Feat 1, Feat 3	Feat 2, Feat 4		Feat 5
APPMMM	Feat 1, Feat 3	Feat 2, Feat 4		Feat 5
Integration	Feat 1, Feat 3	Feat 2, Feat 4		Feat 5

[v-yiruan-msft · Accepted solution]

Hi @kinitundi ,

You can create a measure as below to get it, please find the details in the attachment.

Measure = CONCATENATEX ( VALUES ( 'Table'[Project 2] ), 'Table'[Project 2], "," )

Best Regards

[Ashish_Mathur · Accepted solution]

Hi,

Just in case you want a Power Query solution, then this solution works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Project 2", type text}, {"Target Date", type date}, {"Days Count", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project", "Days Count"}, {{"Projects", each Text.Combine([Project 2],",")}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"Days Count", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"Days Count", type text}}, "en-IN")[#"Days Count"]), "Days Count", "Projects")
in
    #"Pivoted Column"

Hope this helps.

[kinitundi]

Thank you very much for your reply. it worked

[Ashish_Mathur · Accepted solution]

Hi,

Just in case you want a Power Query solution, then this solution works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Project 2", type text}, {"Target Date", type date}, {"Days Count", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project", "Days Count"}, {{"Projects", each Text.Combine([Project 2],",")}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"Days Count", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"Days Count", type text}}, "en-IN")[#"Days Count"]), "Days Count", "Projects")
in
    #"Pivoted Column"

Hope this helps.

[kinitundi]

Thank you Ashish

[Ashish_Mathur]

You are welcome.

[v-yiruan-msft · Accepted solution]

Hi @kinitundi ,

You can create a measure as below to get it, please find the details in the attachment.

Measure = CONCATENATEX ( VALUES ( 'Table'[Project 2] ), 'Table'[Project 2], "," )

Best Regards

[kinitundi]

Thank you very much

[v-yiruan-msft · Accepted solution]

Hi  @kinitundi ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards