Flattening and pivot dataset

[RobbeVL]

Hi,

I've been reading several posts on this subject, but can't seem to find something matching my case enough. (maybe becouse i'm not experienced enough, for you to judge) --> this is a Power query question

I created a report with the data deliverd in a excel friendly way. So I created the report with that structure.

With columns like:

Category - Segment - Jan 2017 - Feb 2017 - Mar 2017 - .....

Now an extra datasource is added, where the data is structured in a more PowerBI manner
(at the moment I do this manually with a formula, but I'd like to see this automated in Pquery)

Date - Category - Segemnt - Value 1 - Value 2

My question now is, how to I pivot my data set to the same structure I used before. (the non PowerBI way I guess) 
Below you can find an illustration of my dataset and the to be dataset:

This is the to-be data set

This is the original dataset. The difficult part is the 4 values. 1 line here should become 4 lines in the to-be dataset
1 with "UniekeKlanten" and 1 with "KPI_Waarde" (and their targets)
Then everything needs to be flattened to the screenshot above.
This data is also dynamic, so new months will be added continuesly, so also need to be taken into account.
The date source is a foler, so new datasets will be added. The target is normally not added to this, but afterwards added manually. As a refresh will delete this, I'll have to implement this in the Powerquery too.

I added the current code  if this might be usefull:

let
    Source = Folder.Files("R:\Datakoepel\BDC\Query Data\CCDATA"),
    #"Invoke Custom Function1" = Table.AddColumn(Source, "Transform File from Finance", each #"Transform File from Finance"([Content])),
    #"Filtered Rows" = Table.SelectRows(#"Invoke Custom Function1", each not Text.StartsWith([Name], "~")),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Finance"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Finance", Table.ColumnNames(#"Transform File from Finance"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type any}, {"Column7", type text}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Columns",{"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Other Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Blank Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Periode", type text}, {"KPI", type text}, {"BDC_Segment", type text}, {"UniekeKlanten", Int64.Type}, {"KPI_Waarde", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Segment", each if [BDC_Segment] = "BXL" then "KBC Brussels" else if [BDC_Segment] = "CBC" then "CBC" else if [BDC_Segment] = "COR" then "Corporate banking" else if [BDC_Segment] = "OND" then "Ondernemers" else if [BDC_Segment] = "PAR" then "Particulieren" else if [BDC_Segment] = "PBK" then "Private banking" else if [BDC_Segment] = "VMG" then "Vermogenden" else if [BDC_Segment] = "VMG - PAB" then "Vermogenden-Particuliere beleggers" else if [BDC_Segment] = "VMG - PMB" then "Vermogende-Premium bankers" else "error"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Periode", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "KEY", each Text.Combine({Text.Start([Periode],4),

(if Text.End([Periode],2) = "01" then "JAN" else
if Text.End([Periode],2) = "02" then "FEB" else
if Text.End([Periode],2) = "03" then "MRT" else
if Text.End([Periode],2) = "04" then "APR" else
if Text.End([Periode],2) = "05" then "MAY" else
if Text.End([Periode],2) = "06" then "JUN" else
if Text.End([Periode],2) = "07" then "JUL" else
if Text.End([Periode],2) = "08" then "AUG" else
if Text.End([Periode],2) = "09" then "SEP" else
if Text.End([Periode],2) = "10" then "OKT" else
if Text.End([Periode],2) = "11" then "NOV" else "DEC" 
),[KPI],[Segment]}, "_")),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom1", {"UniekeKlanten", "KPI_Waarde"})
in
    #"Removed Errors"

Also here you have a sample dataset of the "as is" and "to be" version. (as suggested@Greg_Deckler)

https://www.dropbox.com/s/jvw7zu83xxtdgyd/Sample%20As%20IS%203.0.xlsx?dl=0

https://www.dropbox.com/s/pmgu8w4o3magmz2/Sample%20To%20Be%203.0.xlsx?dl=0

If anyone could help, this would be mostly appreciated!

Regards,

Robbe

[RobbeVL]

Hi,

I've been reading several posts on this subject, but can't seem to find something matching my case enough. (maybe becouse i'm not experienced enough, for you to judge) --> this is a Power query question

I created a report with the data deliverd in a excel friendly way. So I created the report with that structure.

With columns like:

Category - Segment - Jan 2017 - Feb 2017 - Mar 2017 - .....

Now an extra datasource is added, where the data is structured in a more PowerBI manner
(at the moment I do this manually with a formula, but I'd like to see this automated in Pquery)

Date - Category - Segemnt - Value 1 - Value 2

My question now is, how to I pivot my data set to the same structure I used before. (the non PowerBI way I guess) 
Below you can find an illustration of my dataset and the to be dataset:

This is the to-be data set

This is the original dataset. The difficult part is the 2 values. 1 line here should become 2 lines in the to-be dataset
1 with "UniekeKlanten" and 1 with "KPI_Waarde"
Then everything needs to be flattened to the screenshot above.
This data is also dynamic, so new months will be added continuesly, so also need to be taken into account.

I added the current code  if this might be usefull:

let
    Source = Folder.Files("R:\Datakoepel\BDC\Query Data\CCDATA"),
    #"Invoke Custom Function1" = Table.AddColumn(Source, "Transform File from Finance", each #"Transform File from Finance"([Content])),
    #"Filtered Rows" = Table.SelectRows(#"Invoke Custom Function1", each not Text.StartsWith([Name], "~")),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Finance"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Finance", Table.ColumnNames(#"Transform File from Finance"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type any}, {"Column7", type text}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Columns",{"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Other Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Blank Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Periode", type text}, {"KPI", type text}, {"BDC_Segment", type text}, {"UniekeKlanten", Int64.Type}, {"KPI_Waarde", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Segment", each if [BDC_Segment] = "BXL" then "KBC Brussels" else if [BDC_Segment] = "CBC" then "CBC" else if [BDC_Segment] = "COR" then "Corporate banking" else if [BDC_Segment] = "OND" then "Ondernemers" else if [BDC_Segment] = "PAR" then "Particulieren" else if [BDC_Segment] = "PBK" then "Private banking" else if [BDC_Segment] = "VMG" then "Vermogenden" else if [BDC_Segment] = "VMG - PAB" then "Vermogenden-Particuliere beleggers" else if [BDC_Segment] = "VMG - PMB" then "Vermogende-Premium bankers" else "error"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Periode", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "KEY", each Text.Combine({Text.Start([Periode],4),

(if Text.End([Periode],2) = "01" then "JAN" else
if Text.End([Periode],2) = "02" then "FEB" else
if Text.End([Periode],2) = "03" then "MRT" else
if Text.End([Periode],2) = "04" then "APR" else
if Text.End([Periode],2) = "05" then "MAY" else
if Text.End([Periode],2) = "06" then "JUN" else
if Text.End([Periode],2) = "07" then "JUL" else
if Text.End([Periode],2) = "08" then "AUG" else
if Text.End([Periode],2) = "09" then "SEP" else
if Text.End([Periode],2) = "10" then "OKT" else
if Text.End([Periode],2) = "11" then "NOV" else "DEC" 
),[KPI],[Segment]}, "_")),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom1", {"UniekeKlanten", "KPI_Waarde"})
in
    #"Removed Errors"

If anyone could help, this would be mostly appreciated!

Regards,

Robbe

[v-huizhn-msft]

Hi @RobbeVL,

If there is any relationship between the two tables?

Best Regards,
Angelia

[RobbeVL]

Hi,

I had to create a second post, becouse the last one was first marked as spam
This is most recent post with sample files
https://community.powerbi.com/t5/Desktop/Flattening-and-pivot-dataset/m-p/481460#M224126

[ImkeF]

Hi @RobbeVL,

happy to help, but your "As is"-sample dataset isn't available any more.

could you please re-upload it?

Thnks

[ImkeF]

Hi @RobbeVL,

please provide sample data, that actually belong together. 

Thx.

[RobbeVL]

@ImkeF This time I provided what you requested. Also made a slight modification concerning targets (see above)

[ImkeF]

Hi @RobbeVL,

there is no way that Power Query or Power BI can work on multiple columns with the same name. Column names must work as identifiers. So how do you like your columns to be named?

[ImkeF]

The general method is to:

1. Unpivot your 4 "Amount"-columns
2. Pivot on the date column

See this example:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Periode", Int64.Type}, {"KPI", type text}, {"BDC_Segment", type text}, {"UniekeKlanten", Int64.Type}, {"KPI_Waarde", type number}, {"UniekeKlanten_Target", Int64.Type}, {"KPI_Target", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Periode", "KPI", "BDC_Segment"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Periode", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Periode", type text}}, "en-GB")[Periode]), "Periode", "Value")
in
    #"Pivoted Column"

Do you need the empty rows?

[RobbeVL]

Hi Imke, the year could be added in that case (JAN 2017, FEB 2017,...)

I do not need the empty rows. in the sample the empty row was just to show difference 

[ImkeF]

So please check this out then:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Periode", type text}, {"KPI", type text}, {"BDC_Segment", type text}, {"UniekeKlanten", Int64.Type}, {"KPI_Waarde", type number}, {"UniekeKlanten_Target", Int64.Type}, {"KPI_Target", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Periode] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Date", each #date(Number.From(Text.Start([Periode],4)), Number.From(Text.End([Periode],2)),1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Text.Start(Date.MonthName([Date]),3) & " " & Text.From( Date.Year([Date]))),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Added Custom1",{{"Date", Order.Ascending}})),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Periode", "Date"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Custom", "KPI", "BDC_Segment"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Custom", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Custom", type text}}, "en-GB")[Custom]), "Custom", "Value")
in
    #"Pivoted Column"

[RobbeVL]

Great!

I added some things to get to a similar layout and I'm almost there!

Would it be possible to keep the empty rows anyway? when for example eno target is set, the row target will still be there, but empty?

After that the target should always be the row under the value.

let
    Source = Excel.Workbook(File.Contents("C:\Users\JE34562\OneDrive - KBC Group\Public\Sample As IS 3.0.xlsx"), null, true),
    CCDATA_Sheet = Source{[Item="CCDATA",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(CCDATA_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Periode", type text}, {"KPI", type text}, {"BDC_Segment", type text}, {"UniekeKlanten", Int64.Type}, {"KPI_Waarde", type number}, {"UniekeKlanten_Target", Int64.Type}, {"KPI_Target", Int64.Type}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Periode] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Date", each #date(Number.From(Text.Start([Periode],4)), Number.From(Text.End([Periode],2)),1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Text.Start(Date.MonthName([Date]),3) & " " & Text.From( Date.Year([Date]))),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Added Custom1",{{"Date", Order.Ascending}})),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Periode", "Date"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Custom", "KPI", "BDC_Segment"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Custom", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Custom", type text}}, "en-GB")[Custom]), "Custom", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column","BXL","XXX Brussels",Replacer.ReplaceText,{"BDC_Segment"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","VMG","Vermogenden",Replacer.ReplaceText,{"BDC_Segment"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","OND","Ondernemingen",Replacer.ReplaceText,{"BDC_Segment"}),
    #"Added Custom2" = Table.AddColumn(#"Replaced Value2", "Column", each "CCDATA"),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom2",{"Column", "KPI", "BDC_Segment", "Attribute", "jan 2017", "feb 2017", "maa 2017", "jan 2018", "feb 2018", "maa 2018", "apr 2018", "mei 2018", "jun 2018", "jul 2018", "aug 2018", "sep 2018", "okt 2018", "nov 2018", "dec 2018"}),
    #"Added Custom3" = Table.AddColumn(#"Reordered Columns1", "Group", each if Text.Contains([Attribute], "UniekeKlanten") then "UniekeKlanten" else ""),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Type", each if Text.Contains([Attribute], "Target") then "target" else ""),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom4",{"Column", "Group", "KPI", "Type", "BDC_Segment", "Attribute", "jan 2017", "feb 2017", "maa 2017", "jan 2018", "feb 2018", "maa 2018", "apr 2018", "mei 2018", "jun 2018", "jul 2018", "aug 2018", "sep 2018", "okt 2018", "nov 2018", "dec 2018"}),
    #"Added Custom5" = Table.AddColumn(#"Reordered Columns", "Key", each Text.Combine({[Column],"_",[Group],"_",[KPI],"_",[BDC_Segment], "_", [Attribute]}, "")),
    #"Reordered Columns2" = Table.ReorderColumns(#"Added Custom5",{"Key", "Column", "Group", "KPI", "Type", "BDC_Segment", "Attribute", "jan 2017", "feb 2017", "maa 2017", "jan 2018", "feb 2018", "maa 2018", "apr 2018", "mei 2018", "jun 2018", "jul 2018", "aug 2018", "sep 2018", "okt 2018", "nov 2018", "dec 2018"})
in
    #"Reordered Columns2"

[ImkeF]

Yes: Before unpivoting, check the 4 amount-columns and replace null by something special like "****". 

Then, as the last step, check all columns and replace "****" back to null. 

Replace the hardcoded column names by a dynamic expression like this: Table.ColumnNames(<YourPreviousStepName>)

[RobbeVL]

seemed to work, but I do get errors .

[ImkeF]

Thats because you have duplicate rows. For example 201801 E-Voorstellen comes twice. The empty fields are fillled with the replacers and the duplicates are kept. 

So you have to consolidate them first using Table.Group-operations.

[RobbeVL]

Hi @ImkeFImke,
I'm not really familiar with Grouping. What columns should I group? 

Cant see to get it done 😕
I suppose I have to group after unpivoting?

regards,

rob

[ImkeF]

sorry, too busy currently. Will try to look into this at the weekend!

[ImkeF · Accepted solution]

Not so easy, I must admit:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Periode", type text}, {"KPI", type text}, {"BDC_Segment", type text}, {"UniekeKlanten", Int64.Type}, {"KPI_Waarde", type number}, {"UniekeKlanten_Target", Int64.Type}, {"KPI_Target", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Periode] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Date", each #date(Number.From(Text.Start([Periode],4)), Number.From(Text.End([Periode],2)),1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Text.Start(Date.MonthName([Date]),3) & " " & Text.From( Date.Year([Date]))),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Added Custom1",{{"Date", Order.Ascending}})),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Periode", "Date"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"KPI", "BDC_Segment", "Custom"}, {{"All", each Table.LastN(Table.FillDown(Table.Sort(_, {"KPI_Target"}), {"UniekeKlanten", "KPI_Waarde"}), 1), type table}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"UniekeKlanten", "KPI_Waarde", "UniekeKlanten_Target", "KPI_Target"}, {"UniekeKlanten", "KPI_Waarde", "UniekeKlanten_Target", "KPI_Target"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded All",null,"****",Replacer.ReplaceValue,{"UniekeKlanten", "KPI_Waarde", "UniekeKlanten_Target", "KPI_Target"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Custom", "KPI", "BDC_Segment"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Custom", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Custom", type text}}, "en-GB")[Custom]), "Custom", "Value"),
    #"Replaced Value1" = Table.ReplaceValue(#"Pivoted Column","****",null,Replacer.ReplaceValue,Table.ColumnNames(#"Pivoted Column"))
in
    #"Replaced Value1"

  Works if there is max. 2 same items in the table.

[v-frfei-msft]

Hi @RobbeVL,

Based on my test, we can use the Pivot Column function in power query to meet your requirement.

And here is the M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdCxCsIwEAbgV5GAW4Qkl6hrUsGhaqSWIikZOriVTr4/3lk1RW64C4SPn5/re2GU3isjpPDD9BzGVT3i+5jwI9xPuDWOARBZfixwtmoqcjhgwRZsWRwbgm9cqONovBxwU4pdVNhy9Oop1c0VvnanNGtDjZtiYEHZK3Tn41wUs8EVzJ4hpbRpY+s9Xc6Y/y4UErrb77aW2moj3Vrk/AI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Periode = _t, KPI = _t, BDC_Segment = _t, UniekeKlanten = _t, KPI_Waarde = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Periode", Int64.Type}, {"KPI", type text}, {"BDC_Segment", type text}, {"UniekeKlanten", Int64.Type}, {"KPI_Waarde", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [BDC_Segment] = "BXL" then "KBC Brussels" else if [BDC_Segment] = "CBC" then "CBC" else if [BDC_Segment] = "COR" then "Corporate banking" else if [BDC_Segment] = "OND" then "Ondernemers" else if [BDC_Segment] = "PAR" then "Particulieren" else if [BDC_Segment] = "PBK" then "Private banking" else if [BDC_Segment] = "VMG" then "Vermogenden" else if [BDC_Segment] = "VMG - PAB" then "Vermogenden-Particuliere beleggers" else if [BDC_Segment] = "VMG - PMB" then "Vermogende-Premium bankers" else "error"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] <> "error")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Periode", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each if Text.End([Periode],2) = "01" then "JAN" else
if Text.End([Periode],2) = "02" then "FEB" else
if Text.End([Periode],2) = "03" then "MRT" else
if Text.End([Periode],2) = "04" then "APR" else
if Text.End([Periode],2) = "05" then "MAY" else
if Text.End([Periode],2) = "06" then "JUN" else
if Text.End([Periode],2) = "07" then "JUL" else
if Text.End([Periode],2) = "08" then "AUG" else
if Text.End([Periode],2) = "09" then "SEP" else
if Text.End([Periode],2) = "10" then "OKT" else
if Text.End([Periode],2) = "11" then "NOV" else "DEC"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom1", List.Distinct(#"Added Custom1"[Custom.1]), "Custom.1", "KPI_Waarde", List.Sum)
in
#"Pivoted Column"

Regards,

Frank

[RobbeVL]

Thank you for your reply.
This doesnt help me much, I only get 1 value per row, while there should be 1 per month...

Also the 2 different values are not taken into account

[Greg_Deckler]

If you can post some sample source data as text or a file link, @ImkeF is probably your best bet to solve this.