Help with Pivot in Power Query

[Anonymous]

Hi everyone ! 

I have a dataset like this:

Each MEMBER_ID represent a different field, i want to pivot this column.

No problem in doing this: 

The problem is the collumn's names, they can change in the dataset so i don't want to rename them manually as they will be errors if a field is missing. In order to fix that i have in another table the name linked to the id:

So with a Table.NestedJoin() i achieve this:

But now (after deleting the ID collumn), if i want to pivot: 

 = Table.Pivot(#"Colonnes supprimées2", List.Distinct(#"Colonnes supprimées2"[DB_Names.Name]), "DB_Names.Name", "Value", List.Sum)

It throws an error:

The strange part is if i add a Table.Distinct(#"Colonnes supprimées2", {"Date", "DB_Names.Name"}) just before pivoting, It works ! :

Of course i cant really do that as it does remove some data AND i have a bunch of other fields other than 'value' to include later.

Do you have some clues about what is happening ?

EDIT 1: I can now confirm it comes from the SQL request, as i copied the table before pivoting into a new report and i managed to pivot fine.

Here is the new copied report ( i cannot give you the sql request) : https://drive.google.com/file/d/1xiK0DjfC9EQrQ0Zr4G3nJsUcqAVqdYod/view?usp=sharing 

[Anonymous · Accepted solution]

So i figured it out ! before pivoting this collumn i had to make sure there wasn't any duplicates, and in my data set i have another collumn to pivot, i did the other one first and then it allowed my to pivot my 'names' collumn. This is not a problem with the operations but with the SQL query. I don't really know how it works but it seams to adapt the SQL query to your operations and couldn't figure out how to process this pivot without the other.
I wish there was some explanation about how power query really works in that regard, because it got me stuck and it took me some trial and error to figure it out.

[Fowmy]

@Anonymous 

Can you share a sample PBIX file here, save it in OneDrive or any other location, and share the link here. 

[Anonymous]

@Fowmy  Hi,

I've edited the main post,

thx

[Fowmy]

@Anonymous 

What is the expected result based on your table below: