Data transformation question

[JamX]

Dear all,

I'm not as good in power query, hence my question to you all.

I have this excel data set:
All "Names" are unique
"Function" and "Shift" are non unique text fields

Name	Function	Shift
A	A	551
B	A	552
C	A	553
D	A	554
E	A	555
F	B	551
G	B	552
H	C	553
I	C	554
J	C	555
K	C	551
L	C	552
M	C	553
N	C	554
O	C	555

I want/need:
Based on "Function" and "Shift" a table with corresponding "Name"

	Shift
Function	551	552	553	554	554
A	A	B	C	D	E
B	F	F
C			H	I	J
C	K	L	M	N	O

Appreciate any help in this matter.

 

[JamX]

I already have this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Function", type text}, {"Shift", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Function", "Shift"}, {{"Count", each _, type table [Name=nullable text, Function=nullable text, Shift=nullable number]}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"Shift", type text}}, "nl-NL"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"Shift", type text}}, "nl-NL")[Shift]), "Shift", "Count")
in
    #"Pivoted Column"

which gives me this result


but when I expand all 55x column tables I get:

 

[p45cal]

Would something like this do?:

It's concatenatex in the data model.

 

[JamX]

Would something like this do?:
View attachment 80358
It's concatenatex in the data model.

I already found that 'easy' function but the comma separation is not working for me.
I need the names in a separate row.

 

[p45cal]

I already found that 'easy' function

Yes, I too find that going the easy way often ends up being the most robust.

Anyway, try this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Shift", type text}}),
    ShiftHdrs = List.Distinct(ChangedType[Shift]),
    #"Added Index" = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Index", {{"Shift", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Added Index", {{"Shift", type text}}, "en-GB")[Shift]), "Shift", "Name"),
    #"Grouped Rows" = Table.Group(#"Pivoted Column", {"Function"}, {{"grp", each _, type table [Function=text, Index=number, 551=nullable text, 552=nullable text, 553=nullable text, 554=nullable text, 555=nullable text]}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fnBunchUp", each fnBunchUp([grp])),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"grp"}),
    #"Expanded fnBunchUp" = Table.ExpandTableColumn(#"Removed Columns", "fnBunchUp", ShiftHdrs)
in
    #"Expanded fnBunchUp"

which needs its attendant function named fnBunchUp:

(tbl)=>
let
    Source = tbl,
    #"Removed Columns" = Table.RemoveColumns(Source,{"Function", "Index"}),
    ToCols = Table.ToColumns(#"Removed Columns"),
    NewTable = Table.FromColumns(List.Transform(ToCols, each List.RemoveNulls(_)), Table.ColumnNames(#"Removed Columns"))
in
    NewTable

I get from this:


this:

 

[JamX]

Yes, I too find that going the easy way often ends up being the most robust.

Anyway, try this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Shift", type text}}),
    ShiftHdrs = List.Distinct(ChangedType[Shift]),
    #"Added Index" = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Index", {{"Shift", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Added Index", {{"Shift", type text}}, "en-GB")[Shift]), "Shift", "Name"),
    #"Grouped Rows" = Table.Group(#"Pivoted Column", {"Function"}, {{"grp", each _, type table [Function=text, Index=number, 551=nullable text, 552=nullable text, 553=nullable text, 554=nullable text, 555=nullable text]}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fnBunchUp", each fnBunchUp([grp])),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"grp"}),
    #"Expanded fnBunchUp" = Table.ExpandTableColumn(#"Removed Columns", "fnBunchUp", ShiftHdrs)
in
    #"Expanded fnBunchUp"

which needs its attendant function named fnBunchUp:

(tbl)=>
let
    Source = tbl,
    #"Removed Columns" = Table.RemoveColumns(Source,{"Function", "Index"}),
    ToCols = Table.ToColumns(#"Removed Columns"),
    NewTable = Table.FromColumns(List.Transform(ToCols, each List.RemoveNulls(_)), Table.ColumnNames(#"Removed Columns"))
in
    NewTable

I get from this:
View attachment 80359

this:
View attachment 80360

That almost worked, thanks.

the original database is a bit bigger and the outcome is also a bit different then my original question. I thought I could code it by myself but it's more complicated then I thought.
I have attached the file so you can see what I mean.

I would appreciate it if you could make code something for me which resembles the example how to be sheet

 

[p45cal]

and the outcome is also a bit different

A bit?!
Completely different.
It's hard work and the output layout is horrible.
Anyway, in the attached I've shown the beginnings of a different way. It's a slight cheat.
The Pivot table at cell O2 actually has a hidden column (P) called RowNo. Its value is created in the Power Query query and is an index added to the data based on the grouping that will finally be used in the pivot table (there's no reason you can't add other RowNo type columns based on other groupings).
The grouping I've used is Groep and Ploeg (you could add columns say for Groep,Functie & Ploeg (this will allow you to add a Functie column in the pivot), or for only Functie & Ploeg, and use them in place of my RowNo for those groupings).
The pivot uses CONCATENATEX but the RowNo ensures that only a single name appears in each cell.
You've got some work to do adding columns to the input database like you have with Groep to match the example groupings. Then you'll be able to add subtotals within the pivot.

 

[JamX]

A bit?!
Completely different.
It's hard work and the output layout is horrible.
Anyway, in the attached I've shown the beginnings of a different way. It's a slight cheat.
The Pivot table at cell O2 actually has a hidden column (P) called RowNo. Its value is created in the Power Query query and is an index added to the data based on the grouping that will finally be used in the pivot table (there's no reason you can't add other RowNo type columns based on other groupings).
The grouping I've used is Groep and Ploeg (you could add columns say for Groep,Functie & Ploeg (this will allow you to add a Functie column in the pivot), or for only Functie & Ploeg, and use them in place of my RowNo for those groupings).
The pivot uses CONCATENATEX but the RowNo ensures that only a single name appears in each cell.
You've got some work to do adding columns to the input database like you have with Groep to match the example groupings. Then you'll be able to add subtotals within the pivot.

thx for this sollution.
I'll try to make the best of it

great trick btw.