Combine multiple columns

[frankhofmans]

hi PBI community,

i have the following data table (> 300.000 rows):

Client ID	Task	Amount	Inv. Nr 1	Date inv. Nr 1	Inv. Nr 2	Date inv. Nr 2
a001	intake	30	I2022001	1-1-2021	blank	blank
a001	contact	20	I2022001	1-1-2021	blank	blank
a001	consult	100	I2022001	1-1-2021	blank	blank
a001	contact	-20	I2022001	1-1-2021	I2022002	1-2-2021
a001	intake	30	I2022002	1-2-2021	blank	blank

When a task isn't credited, the colums Inv. nr 2 is blank. When a task is credited, the number of the new invoice is filled in in colums Inv. nr 2 and the amount changes to a negative number. In this case, whe originally send a invoice (Inv. nr I2022001) with an amount of EUR 150 and a new invoice (I2022002) with an amount of EUR 10 (EUR 30 and the credited EUR -20). Is there a possibility to merge or expand the colums to get 1 column with the right invoice number?

Many thanks in advance,

Regards,

Frank

[Vera_33 · Accepted solution]

Hi @frankhofmans 

So is it what you want? Added new column instead of tranforming the original

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjQwMFTSUcrMK0nMTgUyjA2AhKeRgZERRMLAUB+IgHwQJyknMS8bTsfqwLUn5wP1J5cAWUbk6i8uzQHpNzSg0AG6eF0AFTeCihtBxJEMwhoQaMox3BELAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Client ID" = _t, Task = _t, Amount = _t, #"Inv. Nr 1" = _t, #"Date inv. Nr 1" = _t, #"Inv. Nr 2" = _t, #"Date inv. Nr 2" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Inv", each if [Inv. Nr 2]="blank" then [Inv. Nr 1] else [Inv. Nr 2]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Date", each if [Date inv. Nr 2] ="blank" then [Date inv. Nr 1] else [Date inv. Nr 2]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Date", type date}, {"Amount", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Inv. Nr 1", "Date inv. Nr 1", "Inv. Nr 2", "Date inv. Nr 2"})
in
    #"Removed Columns"