Forum Discussion
Solved
structure table
Good morning, I need to convert a table into a power query editor as follows:
Initial table:
| null | null | mes1 | mes1 | mes2 | mes2 | mes3 | mes3 |
| null | null | first fortnight | second fortnight | first fortnight | second fortnight | first fortnight | second fortnight |
| person 1 | tipo1 | 1 | 2 | 3 | 4 | 5 | 6 |
| persona1 | tipo2 | 5 | 8 | 4 | 3 | 8 | 25 |
| person 2 | tipo1 | 1 | 2 | 3 | 4 | 5 | 6 |
| persona2 | tipo2 | 5 | 8 | 4 | 3 | 8 | 25 |
| person 3 | tipo1 | 1 | 2 | 3 | 4 | 5 | 6 |
| persona3 | tipo2 | 5 | 8 | 4 | 3 | 8 | 25 |
Table to get:
| person | guy | my | fortnight | value |
| persona1 | tipo1 | mes1 | first fortnight | 1 |
| persona1 | tipo1 | mes1 | second fortnight | 2 |
| persona1 | tipo1 | mes2 | first fortnight | 3 |
| persona1 | tipo1 | mes2 | second fortnight | 4 |
| persona1 | tipo1 | mes3 | first fortnight | 5 |
| persona1 | tipo1 | mes3 | second fortnight | 6 |
| persona1 | tipo2 | mes1 | first fortnight | 5 |
| persona1 | tipo2 | mes1 | second fortnight | 8 |
| persona1 | tipo2 | mes2 | first fortnight | 4 |
| persona1 | tipo2 | mes2 | second fortnight | 3 |
| persona1 | tipo2 | mes3 | first fortnight | 8 |
| persona1 | tipo2 | mes3 | second fortnight | 25 |
| persona2 | tipo1 | mes1 | first fortnight | 1 |
| persona2 | tipo1 | mes1 | second fortnight | 2 |
| persona2 | tipo1 | mes2 | first fortnight | 3 |
| persona2 | tipo1 | mes2 | second fortnight | 4 |
| persona2 | tipo1 | mes3 | first fortnight | 5 |
| persona2 | tipo1 | mes3 | second fortnight | 6 |
| persona2 | tipo2 | mes1 | first fortnight | 5 |
| persona2 | tipo2 | mes1 | second fortnight | 8 |
| persona2 | tipo2 | mes2 | first fortnight | 4 |
| persona2 | tipo2 | mes2 | second fortnight | 3 |
| persona2 | tipo2 | mes3 | first fortnight | 8 |
| persona2 | tipo2 | mes3 | second fortnight | 25 |
| persona3 | tipo1 | mes1 | first fortnight | 1 |
| persona3 | tipo1 | mes1 | second fortnight | 2 |
| persona3 | tipo1 | mes2 | first fortnight | 3 |
| persona3 | tipo1 | mes2 | second fortnight | 4 |
| persona3 | tipo1 | mes3 | first fortnight | 5 |
| persona3 | tipo1 | mes3 | second fortnight | 6 |
| persona3 | tipo2 | mes1 | first fortnight | 5 |
| persona3 | tipo2 | mes1 | second fortnight | 8 |
| persona3 | tipo2 | mes2 | first fortnight | 4 |
| persona3 | tipo2 | mes2 | second fortnight | 3 |
| persona3 | tipo2 | mes3 | first fortnight | 8 |
| persona3 | tipo2 | mes3 | second fortnight | 25 |
I hope I have explained myself.
Thank you very much in advance
Syndicate_Admin
Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSgVG5qcWGKJQRCmUMo2J10HWmZRYVlyik5ReV5GWmZ5QARYpTk/PzUlCEqKUIZH1BalFxfp4CyJ0lmQX5IBqEQS4FOdMEiE2B2AxJcSJMsRFU0gKq0BjKNjJFNtqIFKONSDLamBSjjYkwOhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]), #"Transposed Table" = Table.Transpose(Source), #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"), #"Transposed Table1" = Table.Transpose(#"Merged Columns"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"null|null", type text}, {"null|null_1", type text}, {"mes1|first fortnight", Int64.Type}, {"mes1|second fortnight", Int64.Type}, {"mes2|first fortnight", Int64.Type}, {"mes2|second fortnight", Int64.Type}, {"mes3|first fortnight", Int64.Type}, {"mes3|second fortnight", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"null|null", "null|null_1"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"null|null", "Person"}, {"null|null_1", "Guy"}, {"Attribute.1", "My"}, {"Attribute.2", "Fortnight"}}) in #"Renamed Columns"
Watch my video on this topic : https://www.youtube.com/watch?v=_YjFGsS9lNY&t=0s
2 Replies
Syndicate_Admin
Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSgVG5qcWGKJQRCmUMo2J10HWmZRYVlyik5ReV5GWmZ5QARYpTk/PzUlCEqKUIZH1BalFxfp4CyJ0lmQX5IBqEQS4FOdMEiE2B2AxJcSJMsRFU0gKq0BjKNjJFNtqIFKONSDLamBSjjYkwOhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]), #"Transposed Table" = Table.Transpose(Source), #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"), #"Transposed Table1" = Table.Transpose(#"Merged Columns"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"null|null", type text}, {"null|null_1", type text}, {"mes1|first fortnight", Int64.Type}, {"mes1|second fortnight", Int64.Type}, {"mes2|first fortnight", Int64.Type}, {"mes2|second fortnight", Int64.Type}, {"mes3|first fortnight", Int64.Type}, {"mes3|second fortnight", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"null|null", "null|null_1"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"null|null", "Person"}, {"null|null_1", "Guy"}, {"Attribute.1", "My"}, {"Attribute.2", "Fortnight"}}) in #"Renamed Columns"
Watch my video on this topic : https://www.youtube.com/watch?v=_YjFGsS9lNY&t=0sThank you so much
