multiple rows into one row

[chanpreet_90]

I have a data in this format and I want to combine the rows of Details into one row.

please help me ...I am a beginner...I tried using Text.Combine but still getting error..

Thanks in advance  

[KT_Bsmart2gethe]

HI @chanpreet_90 ,

Below link has the solution on how to group text:

https://community.powerbi.com/t5/Power-Query/Add-Column-based-on-VLOOKUP-and-CONCATENTATION/m-p/2659...

Also, if you need to sum the number, you must ensure the credit sign "cr" is gone. Then, you can apply the text replace function before you use GroupBy. 

If you're still struggling, please provide data with sensitive info removed and in a friendly format, and I will get back to you with a step-by-step solution.

Regards

KT

[chanpreet_90]

Thank you for you response my friend..

I tried your solution but now im getting some 'error' in 'details', 'postdate' and 'valuedate'.

I guess the error in details column is coming because it has text and number both.

Please help

Regards

[KT_Bsmart2gethe]

Hi @chanpreet_90 ,

Define the data time before you apply GroupBy. Errors are general cause by a mix of data type. For those errors in the date columns, what are other values recorded under those column apart from date?

If you're apply the text.combine function. The column has to be in text format.

Regards

KT

[chanpreet_90]

Hey...I am able to resolve the issue coming in Details column however still getting Error in date columns. Please check and help

Regards

[KT_Bsmart2gethe]

HI @chanpreet_90 ,

Are you able to share the code? The provided screenshot is insufficient to tell where the errors are from, apart from the errors that said you attempted to add the date. 

Regards

KT

[chanpreet_90]

Helloo

Here is the code : -

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PostDate", type date}, {"ValueDate", type date}, {"Details", type text}, {"ChqNo", Int64.Type}, {"Debit", Int64.Type}, {"Credit", Int64.Type}, {"Balance", type number}}),
#"Filled Down" = Table.FillDown(#"Changed Type",{"Balance"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"Balance"}, {{"PostDate", each List.Sum([PostDate]), type nullable date}, {"ValueDate", each List.Sum([ValueDate]), type nullable date}, {"Details", each Text.Combine([Details], " - "), type nullable text}, {"ChqNo", each List.Sum([ChqNo]), type nullable number}, {"Debit", each List.Sum([Debit]), type nullable number}, {"Credit", each List.Sum([Credit]), type nullable number}})
in
#"Grouped Rows"

[KT_Bsmart2gethe]

Hi @chanpreet_90 ,

Delete the red and move them to blue. I won't try to sum date or cheque no.

#"Grouped Rows" = Table.Group(#"Filled Down", {"Balance", "PostDate", "ValueDate", "ChqNo"}, {{"PostDate", each List.Sum([PostDate]), type nullable date}, {"ValueDate", each List.Sum([ValueDate]), type nullable date}, {"Details", each Text.Combine([Details], " - "), type nullable text}, {"ChqNo", each List.Sum([ChqNo]), type nullable number}, {"Debit", each List.Sum([Debit]), type nullable number}, {"Credit", each List.Sum([Credit]), type nullable number}})

Regards

KT

[chanpreet_90]

Hey..

it worked but in balance column the value is repeating...

Regards