Similar columns in single table and need guidance on how to handle

[dsever04]

Background & Sample Data:

We have data coming in from a single SharePoint list that has multiple transactions on a single row/record for each team.

Record ID	Team	Trans 1 ID	Trans 1 Date	Trans 1 Cost	Trans 2 ID	Trans 2 Date	Trans 2 Cost	Trans 3 ID	Trans 3 Date	Trans 3 Cost
1	Team A	45654	1/5/2019	$1,342.22	15687	4/26/2019	$7,964.50	45897	10/10/2019	$111.12
2	Team B	48996	2/5/2019	$900.00	65489	3/11/2020	$121.84
3	Team C	98764	2/7/2019	$143.55	25489	6/1/2019	$2,319.10
4	Team D	78136	4/15/2019	$684.99	63632	7/6/2019	$75.88	66546	1/5/2020	$600.01

Issues & Questions

In an ideal world, I'd think the data would be best arranged with each transaction listed as an individual row/record. We'd then have a single Trans Date, Trans Cost, and Trans ID column instead of these being repeated across multiple columns. 

Since this isn't our world, what is the best way to go about handling data like this? Our actual data has even more transaction columns repeated (~50 trans total per record). Is there a way to combine columns (e.g. Trans 1 Date, Trans 2 Date, etc.) into a single column (Trans Dates)? Is that even what we should be looking to do? A bit lost with the current format and what we should do to change and shape it into something usable for creating reports/dashboards in Power BI. Any and all guidance and help is greatly appreciated.

[v-eqin-msft · Accepted solution]

Hi @dsever04 ,

Please follow steps as shown below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZA9bgQhDIWvskIpkeEZMHaZnyOkG02RImXu38YmO+xKkUBYfuazn48jIeX0+f31c3v1oA8Z3V+UUbjCPHxBbp2JOdJDdEZZYdn6zCadRl3f1UJHLX42ACBwOvOR+Gr2FtVqJv7yUzOrlWqgfA6NTCuAq1wXiUEa8932DWq7qO8emE7pizofE/RGY0TyTpWCLXJuMEL9h+0X9sODqWiyrOMxrWgnW7wmLbzN8rSXQaqhuRXZO/0zImET6Tx/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Record ID" = _t, Team = _t, #"Trans 1 ID" = _t, #"Trans 1 Date " = _t, #"Trans 1 Cost" = _t, #"Trans 2 ID" = _t, #"Trans 2 Date" = _t, #"Trans 2 Cost" = _t, #"Trans 3 ID" = _t, #"Trans 3 Date" = _t, #"Trans 3 Cost" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Record ID", Int64.Type}, {"Team", type text}, {"Trans 1 ID", Int64.Type}, {"Trans 1 Date ", type date}, {"Trans 1 Cost", Currency.Type}, {"Trans 2 ID", Int64.Type}, {"Trans 2 Date", type date}, {"Trans 2 Cost", Currency.Type}, {"Trans 3 ID", Int64.Type}, {"Trans 3 Date", type date}, {"Trans 3 Cost", Currency.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Record ID", "Team"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3", "Attribute.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}, {"Attribute.3", type text}, {"Attribute.4", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.1", "Attribute.2", "Attribute.4"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Attribute.3]), "Attribute.3", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

Here is the pbix file.

https://kohera.be/blog/power-bi/how-to-unpivot-twice/

https://community.powerbi.com/t5/Power-Query/Expression-Error-There-were-too-many-elements-in-the-en...

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[HotChilli]

@dsever04 

If you select the first 2 columns and 'Unpivot other columns'

You should now have 4 columns.  You want to split the 3rd ('attribute' column) by delimiter (space) so that you can retain the numeral column and the column with ID, Date, Cost.

Remove the column with 'Trans' in it and any other null columns.

You should have 5 columns now.

Pivot on the  column with ID, Date, Cost,    using Value as the Values column.  Choose 'Don't aggregate' in the advanced section.

Filter out the null values in the ID column.

That should work

[DataInsights]

@dsever04,

You can unpivot each set of columns (e.g., Trans 1) using the technique below.

https://datachant.com/2019/10/11/guest-story-unpivot-pairs-of-columns/