need solution to Rearrange with Excel data //attach file

[GhostTIT]

SET	participant1	type	ratio%	participant2	type2	ratio%2	participant3	type3	ratio%3
1	Mr.AAA	Head	50	Mr.John	partner	25	Mr.One	assistant	25
2	Mr.BBB	Head	100
3	Mrs.CCC	Head	80	Mrs.Sarah	assistant	0	Mrs.three	partner	10

[top] I want to rearrange with 50 SET, each Set has 10 participants (3 columns in 1 participant)

I want to transform to data [below]

SET	Participant	Type	Ratio%
1	Mr.AAA	Head	50
1	Mr.John	partner	25
1	Mr.One	assistant	25
2	Mr.BBB	Head	100
3	Mrs.CCC	Head	80
3	Mrs.Sarah	assistant	0
3	Mrs.three	partner	10

how to do it , i try unpivot column many times and get stunning T_T

thank you so much. 🙂

file attach : download excel file 

[Anonymous]

Hi @GhostTIT ,

The first method is to copy out two tables, three tables with different participant, type, and ratio. Then append to ensure that the column names are consistent.

Append queries - Power Query | Microsoft Learn

The second method is as follows.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIt0nN0dAQyPFITU4CUqQFE0Cs/Iw/IKkgsKslLLQKyjEwhEv55qUBGYnFxZnFJYl4JRCZWJ1rJCCLv5OSEMM3QAGQcOgKpNoaoTk5ORqi2gNhdrBecWJSYgWYLTK4koyg1FcVlhgZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SET = _t, participant1 = _t, #"type" = _t, #"ratio%" = _t, participant2 = _t, type2 = _t, #"ratio%2" = _t, participant3 = _t, type3 = _t, #"ratio%3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SET", Int64.Type}, {"participant1", type text}, {"type", type text}, {"ratio%", Int64.Type}, {"participant2", type text}, {"type2", type text}, {"ratio%2", Int64.Type}, {"participant3", type text}, {"type3", type text}, {"ratio%3", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"SET"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("1", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Attribute.1", Splitter.SplitTextByDelimiter("2", QuoteStyle.Csv), {"Attribute.1.1", "Attribute.1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Attribute.1.1", type text}, {"Attribute.1.2", type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Attribute.1.1", Splitter.SplitTextByDelimiter("3", QuoteStyle.Csv), {"Attribute.1.1.1", "Attribute.1.1.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Attribute.1.1.1", type text}, {"Attribute.1.1.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"Attribute.1.1.2", "Attribute.1.2", "Attribute.2"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Attribute.1.1.1", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Attribute.1.1.1"}, {{"Count", each _, type table [#"SET - Copy"=nullable number, Attribute.1.1.1=nullable text, Value=any]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Value", "Index"}, {"Custom.Value", "Custom.Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Attribute.1.1.1]), "Attribute.1.1.1", "Custom.Value"),
    #"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each ([#"ratio%"] <> null))
in
    #"Filtered Rows"

You download my attachment for more details.

Best Regards,

Stephen Tao

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

[GhostTIT]

i've test your code and it really broke T_T 

because If you put no. 1-2-3 behind attribure out (participate,type,ratio) 

the resulf will not connect to the correct position. 

look   the result data have to be

Mr.AAA = Head = 50

but in your result 

Mr. AAA = Head = 10  and other rows also switch so on.. 

[AlienSx]

Hi, @GhostTIT 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    to_rows = List.Buffer(List.Transform(Table.ToRows(Source), (x) => List.RemoveNulls(x))),
    f = (lst as list) =>
        [a = List.First(lst),
        b = List.Split(List.Skip(lst), 3),
        c = List.Transform(b, (x) => {a} & x)][c],
    out = Table.FromRows(List.Combine(List.Transform(to_rows, f)), {"SET", "Participant", "Type", "Ratio%"})
in
    out

[slorin]

Hi

let
Source = YourSource,
Data = Table.AddColumn(Source, "Data", each Table.FromRows(List.Split(List.Skip(Record.ToList(_)),3),{"Participant", "Type", "Ratio%"})),
SelectColumns = Table.SelectColumns(Data,{"SET", "Data"}),
Expand = Table.ExpandTableColumn(SelectColumns, "Data", {"Participant", "Type", "Ratio%"}, {"Participant", "Type", "Ratio%"})
in
Expand

Stéphane 

[GhostTIT]

It's not working ... code is....

--------------------------------------

//OLD FirstRUN
/*
let
Source = Excel.Workbook(File.Contents("C:\Users\Ghost-ROG\Desktop\rearrange_data.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"SET", Int64.Type}, {"participant1", type text}, {"type", type text}, {"ratio%", Int64.Type}, {"participant2", type text}, {"type2", type text}, {"ratio%2", Int64.Type}, {"participant3", type text}, {"type3", type text}, {"ratio%32", Int64.Type}})
in
#"Changed Type"

*/

//NEW *not working
let
Source = Excel.Workbook(File.Contents("C:\Users\Ghost-ROG\Desktop\rearrange_data.xlsx"), null, true),
Data = Table.AddColumn(Source, "Data", each Table.FromRows(List.Split(List.Skip(Record.ToList(_)),3),{"Participant", "Type", "Ratio%"})),
SelectColumns = Table.SelectColumns(Data,{"SET", "Data"}),
Expand = Table.ExpandTableColumn(SelectColumns, "Data", {"Participant", "Type", "Ratio%"}, {"Participant", "Type", "Ratio%"})
in
Expand

-------------------------

At first it show ": The field 'Data' already exists in the record."

then I change Data to Data1 in line2

it show The column 'SET' of the table wasn't found.

What i should do next?

and I don't understand that code. Is it have to write down the whole code?

[slorin]

Hi 

There's a step between Source and Data

Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],

Stéphane