Split all columns with .1 into rows and columns with .2 into rows

[Covington]

I'm sure I can query the table twice and then merge somehow in code, but not sure that is the right way to do this.

My table is like this:

Team.1.1	Team.1.2	Team.2.1	Team 2.2	Val.1	Val.2
501	UTA	502	BRK	-4	200

I want the following:

TeamNumber	TeamName	Value
501	UTA	-4
502	BRK	200

All of the .1 and .2 are from splits (of course) on each column.  Again, I'm sure I can query out each set of columns from the table (2 queries) and then merge with proper headers, but not sure that is the correct solution?

[v-alq-msft · Accepted solution]

Hi, @Covington 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may apply the following transformations to get the final result. Here are the m codes in 'Advanced Editor'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUwVNJRCg1xBJKmBkZA0inIG0jqmgAJIwMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team.1.1 = _t, Team.1.2 = _t, Team.2.1 = _t, #"Team 2.2" = _t, Val.1 = _t, Val.2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team.1.1", Int64.Type}, {"Team.1.2", type text}, {"Team.2.1", Int64.Type}, {"Team 2.2", type text}, {"Val.1", Int64.Type}, {"Val.2", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Team.1.1", type text}, {"Val.1", type text}}, "en-US"),{"Team.1.1", "Team.1.2", "Val.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Team1"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Team.2.1", type text}, {"Val.2", type text}}, "en-US"),{"Team.2.1", "Team 2.2", "Val.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Team2"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"TeamNumber", "TeamName", "Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"TeamNumber", Int64.Type}, {"TeamName", type text}, {"Value", Int64.Type}})
in
    #"Changed Type1"

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ImkeF]

Hi @Covington ,

this is a solution that would cover additional teams and scenarios as well:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUwVNJRCg1xBJKmBkZA0inIG0jqmgAJIwMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team.1.1 = _t, Team.1.2 = _t, Team.2.1 = _t, #"Team 2.2" = _t, Val.1 = _t, Val.2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team.1.1", Int64.Type}, {"Team.1.2", type text}, {"Team.2.1", Int64.Type}, {"Team 2.2", type text}, {"Val.1", Int64.Type}, {"Val.2", Int64.Type}}),
    toLists = Table.ToColumns(#"Changed Type"),
    NumberOfTeams = List.Count(toLists) / 3,
    TeamsColumns = List.Range(toLists, 0, NumberOfTeams * 2),
    #"Converted to Table" = Table.FromList(TeamsColumns, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1, Int64.Type),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 2), type number}}),
    TeamNumbers = Table.SelectRows(#"Calculated Modulo", each ([Index] = 0))[Column1],
    TeamNames = Table.SelectRows(#"Calculated Modulo", each ([Index] = 1))[Column1],
    TeamValues = List.Difference(toLists, TeamsColumns),
    Custom1 = Table.FromColumns({List.Union(TeamNumbers), List.Union(TeamNames), List.Union(TeamValues)})
in
    Custom1

paste the code into the advanced editor and follow the steps.

[ToddChitt]

Can you give us a sample of the ORIGINAL data, before you split it? And in doing splits, is there a way to split into ROWS instead of COLUMNS?