topic Re: need solution to Rearrange with Excel data //attach file in Power Query

need solution to Rearrange with Excel data //attach file

GhostTIT — Tue, 27 Jun 2023 08:45:12 GMT

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

Re: need solution to Rearrange with Excel data //attach file

slorin — Tue, 27 Jun 2023 10:25:02 GMT

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

Re: need solution to Rearrange with Excel data //attach file

GhostTIT — Tue, 27 Jun 2023 15:51:38 GMT

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?

Re: need solution to Rearrange with Excel data //attach file

AlienSx — Wed, 28 Jun 2023 06:02:05 GMT

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

Re: need solution to Rearrange with Excel data //attach file

slorin — Wed, 28 Jun 2023 06:09:22 GMT

There's a step between Source and Data

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

Stéphane

Re: need solution to Rearrange with Excel data //attach file

Anonymous — Thu, 29 Jun 2023 02:59:14 GMT

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.

Re: need solution to Rearrange with Excel data //attach file

GhostTIT — Sat, 01 Jul 2023 07:54:31 GMT

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..