Forum Discussion
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 entries and keep the data in the first 15 columns. In excel I would look to use a VBA to alter the data, but I'd prefer to do this on my end in Power BI as to not transform their data and the way they're using it.
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/
4 Replies
Using the unpivot isn't working. What I'm trying to do with the query is this:
This might not the best efficient way. But try
union( summarize(table, table[project Number] , table[project Name], table[phase], table[Current], table[funded]), summarize(table, table[project Number] , table[project Name], table[phase2], table[Current2], table[funded2]), summarize(table, table[project Number] , table[project Name], table[phase3], table[Current3], table[funded3]), summarize(table, table[project Number] , table[project Name], table[phase4], table[Current4], table[funded4]) )Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
