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.1Team.1.2Team.2.1Team 2.2Val.1Val.2501UTA502BRK-4200 I want the following: TeamNumberTeamNameValue501UTA-4502BRK200 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?

Covington

Helper I

5 years ago

Solved

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

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?

Need Help

v-alq-msft

5 years ago

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.

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

3 Replies

ToddChitt
Super User
5 years ago
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?

ImkeF

Community Champion

5 years ago

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.

v-alq-msft

Community Support

5 years ago

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.

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

Forum Discussion

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

3 Replies

Related content

Splitting Column values

Split a column into two separate columns

Split Columns

Column Splitting

Split and rearrange columns