Need help on adding new rows from colums

[philipsr1]

Need some advice .I have a table with a row contraining three receive dates and three receive amounts. I am looking for a way to convert them into three rows  as shown below

Original

RECACCT	RECDATE1	RECAMT1	RECDATE2	RECAMT1	RECDATE3	RECAMT1
30004	20191113	33009.05	20200417	-41860.25	20200417	41860.25
30005	20200417	23993.71	00000000	0	00000000	0

Desired output

RECACCT  RECDATE              RECAMT

30004        20191113               33009.05

30004        20200417             -41860.25

30004        20200417              41860.25

30005        20200417              23993.71

Any help would be appriciated

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RECACCT", type text}, {"RECDATE1", Int64.Type}, {"RECAMT1", type number}, {"RECDATE2", Int64.Type}, {"RECAMT12", type number}, {"RECDATE3", Int64.Type}, {"RECAMT13", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"RECACCT"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Attribute.2"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"RECACCT", type text}}, "en-IN"),{"RECACCT", "Attribute.1"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Added Index" = Table.AddIndexColumn(#"Merged Columns", "Index", 1, 1),
    Partition = Table.Group(#"Added Index", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value", "Index", "Index1"}, {"Value", "Index", "Index1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "Merged", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Merged.1", "Merged.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Merged.2]), "Merged.2", "Value"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Index1"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns2",{{"RECAMT", type number}})
in
    #"Changed Type2"

Hope this helps.

[parry2k]

@philipsr1 you need to unpivot your table

- click transform data

- select RECACCT column 

- right-click and select unpivot other columns

you will get two new columns, attribute, and value

- rename attribute to RecDate and value to Amount

and now you can visualize the data the way you want. and it is best practice from the modeling perspective.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[philipsr1]

Unfortunately unpivot does not work in my scenario. Unpivot as I understand works for example , when you have data of sales for mutiple Years. So the heading like 2015,2016,2017, 2018 as column headings with sale amounts of each of them under it. So when you unpivot it converts years(the heading) as attributes and sales as values. My case is different. I have
RECDATE1,RECAMT1,RECDATE2,RECAMT2,RECDATE3,RECAMT3 as headings and corresponding values underneath it. and I am looking for a way to create two colums with the value of RECDATE in one column and the corresponding RECAMT value on the other.I made a typo on my heading which might have caused the confusion.

I am entering the input and the desired output

Original
RECACCT           RECDATE1            RECAMT1            RECDATE2     RECAMT2         RECDATE3     RECAMT3
30004                20191113             33009.05             20200417     -41860.25        20200417      41860.25
30005                20200417             23993.71             00000000      0                       00000000      0

Desired output

RECACCT      RECDATE      RECAMT

30004           20191113     33009.05

30004           20200417    -41860.25

30004           20200417     41860.25

30005           20200417     23993.71

Thanks

[camargos88]

Hi @philipsr1 ,

Check this file as an example:  Download PBIX 

Ricardo