Forum Discussion
jritchey
6 years agoFrequent Visitor
Create multiple rows from columns
Hello, I'm currently working with a dataset that is coming in from one of our project offices excel files. The data has 27 columns, I need to take the last 14 columns and create new rows for those...
- 6 years ago
The unpivot-solution in the query editor would look like so:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLvErzU1KLVLSUQrISCxOVTAEspxLi4pS80qALLfSvJTUFLikETbJWJ1oJUcnZ0OQJEi3oYEBkDQFESAhIzALJAhS6OziamJqBlVpZGAAV28E1mMAFY2NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProjectNumber = _t, #"Phase 1" = _t, Current = _t, Funded = _t, Phase2 = _t, asdf = _t, Column2 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ProjectNumber", type text}, {"Phase 1", Int64.Type}, {"Current", Int64.Type}, {"Funded", Int64.Type}, {"Phase 2", Int64.Type}, {"Current_1", Int64.Type}, {"Funded_2", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ProjectNumber"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Split Column by Delimiter", {{"Attribute.1", each Text.BeforeDelimiter(_, "_"), type text}}), #"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Attribute.2", "Phase"}}), #"Filled Down" = Table.FillDown(#"Renamed Columns",{"Phase"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Attribute.1] <> "Phase")), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute.1]), "Attribute.1", "Value") in #"Pivoted Column"This is following the method I've described here: https://www.thebiccountant.com/2015/08/12/how-to-pivot-multiple-measurescolumns-in-power-query/
jritchey
6 years agoFrequent Visitor
Using the unpivot isn't working. What I'm trying to do with the query is this:
ImkeF
6 years agoCommunity Champion
The unpivot-solution in the query editor would look like so:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLvErzU1KLVLSUQrISCxOVTAEspxLi4pS80qALLfSvJTUFLikETbJWJ1oJUcnZ0OQJEi3oYEBkDQFESAhIzALJAhS6OziamJqBlVpZGAAV28E1mMAFY2NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProjectNumber = _t, #"Phase 1" = _t, Current = _t, Funded = _t, Phase2 = _t, asdf = _t, Column2 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ProjectNumber", type text}, {"Phase 1", Int64.Type}, {"Current", Int64.Type}, {"Funded", Int64.Type}, {"Phase 2", Int64.Type}, {"Current_1", Int64.Type}, {"Funded_2", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ProjectNumber"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Split Column by Delimiter", {{"Attribute.1", each Text.BeforeDelimiter(_, "_"), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Attribute.2", "Phase"}}),
#"Filled Down" = Table.FillDown(#"Renamed Columns",{"Phase"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Attribute.1] <> "Phase")),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute.1]), "Attribute.1", "Value")
in
#"Pivoted Column"
This is following the method I've described here: https://www.thebiccountant.com/2015/08/12/how-to-pivot-multiple-measurescolumns-in-power-query/