Forum Discussion
Deliminate columns but keep like values together
No problem. Here's how I think you should be structuring your table for 1) maximum efficiency and 2) ease of reporting:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY3BCoMwEER/Jew5F9svkAbvnkMOkg4aWBNJVtG/79JLqbd5MDPPe+rI0rhPnOTSNHAp1QzplL3Cmr7GJQmi0sQUrKeHll5l3RiCjNYUHVqas3E4wGVbkeXbfP6OrblN+veCihxV8WekED4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"Project Info" = _t, Design = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Project Name", Int64.Type}, {"Project Info", type text}, {"Design", type text}}), // Relevant steps -----> unpivOthCols = Table.UnpivotOtherColumns(chgTypes, {"Project Name"}, "improvArea", "Value"), addImprovItem = Table.AddColumn(unpivOthCols, "improvItem", each Text.Split([Value], ", ")), expandImprovItem = Table.ExpandListColumn(addImprovItem, "improvItem"), // <----- Relevant steps remOthCols = Table.SelectColumns(expandImprovItem,{"Project Name", "improvArea", "improvItem"}) in remOthColsThis gives us the following fact table output structure:
This fact structure then makes calculations over the whole table very easy, for example:
_noofProjects = DISTINCTCOUNT(factProjects[Project Name]) _noofImprovAreas = DISTINCTCOUNT(factProjects[improvArea]) _noofImprovItems = COUNTROWS(factProjects)These measures can be used in visuals with any of your fact table columns to provide the insights you're looking for. The way you were asking for your data to be structured previously would have required a separate measure for each [Project Info ~] column, and each [Design ~] column, which would have quickly become difficult to maintain, challenging to get visuals to show what you wanted, and impossible for improvement areas and improvement items to be used in slicers etc.
Above the fact table you would likely have a dimProject dimension table which would have a single row per [Project Name], and would contain info such as Project Manager, Client, Project Start Date/End Date etc. which would be related to this fact table on Dimension[Project Name] ONE : MANY Fact[Project Name], but this is getting slightly beyond scope of this post I think.
Pete
Hi kene76 ,
While I appreciate that tackytechtom has done a great job in creating what you've asked for, I think this is essentially an XY Problem in the making, and you're likely asking for fundamentally the wrong thing.
Power Query should be used to get data into the most efficient structure possible before being sent to the data model to be reformatted into your desired layout. Your request appears to be trying to create the format/layout in Power Query while moving the data a long way away from an efficient structure.
Are you able to describe/provide examples of the types of visuals you want to display based on this dataset so we can then look at the most efficient (and future-proofed) data structure please?
My initial thought would be to break your table out into separate fact and dimension tables, but would be helpful to ensure this would fit the needs of your desired visuals.
Pete
Thank you for the response and you are most likely correct. I have only been using Power BI for a year and keep getting asked to perform more and more advanced reporting. This data will ultimately report the perfomance of our franchise teams. So we have multiple categories to grade them on and instead of doing a 1 - 5 scoring method, we are giving them a simple pass/fail. If they fail, we call out what specific taks they need to improve - that is what is above. Our project team fills out a form after project completion and they can multi-select everything that needs to be improved which is what the screen shot above shows. I want to separte these out so I can show charts that detail which projects need assistance with Floor Fixtures, for example. The form brings in the data in one cell and comma separates each value. With what I currently know, having each one in a separate column seemed the logical choice.
Thank you for your time.
No problem. Here's how I think you should be structuring your table for 1) maximum efficiency and 2) ease of reporting:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY3BCoMwEER/Jew5F9svkAbvnkMOkg4aWBNJVtG/79JLqbd5MDPPe+rI0rhPnOTSNHAp1QzplL3Cmr7GJQmi0sQUrKeHll5l3RiCjNYUHVqas3E4wGVbkeXbfP6OrblN+veCihxV8WekED4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"Project Info" = _t, Design = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Project Name", Int64.Type}, {"Project Info", type text}, {"Design", type text}}), // Relevant steps -----> unpivOthCols = Table.UnpivotOtherColumns(chgTypes, {"Project Name"}, "improvArea", "Value"), addImprovItem = Table.AddColumn(unpivOthCols, "improvItem", each Text.Split([Value], ", ")), expandImprovItem = Table.ExpandListColumn(addImprovItem, "improvItem"), // <----- Relevant steps remOthCols = Table.SelectColumns(expandImprovItem,{"Project Name", "improvArea", "improvItem"}) in remOthColsThis gives us the following fact table output structure:
This fact structure then makes calculations over the whole table very easy, for example:
_noofProjects = DISTINCTCOUNT(factProjects[Project Name]) _noofImprovAreas = DISTINCTCOUNT(factProjects[improvArea]) _noofImprovItems = COUNTROWS(factProjects)These measures can be used in visuals with any of your fact table columns to provide the insights you're looking for. The way you were asking for your data to be structured previously would have required a separate measure for each [Project Info ~] column, and each [Design ~] column, which would have quickly become difficult to maintain, challenging to get visuals to show what you wanted, and impossible for improvement areas and improvement items to be used in slicers etc.
Above the fact table you would likely have a dimProject dimension table which would have a single row per [Project Name], and would contain info such as Project Manager, Client, Project Start Date/End Date etc. which would be related to this fact table on Dimension[Project Name] ONE : MANY Fact[Project Name], but this is getting slightly beyond scope of this post I think.
Pete