Forum Discussion
Helper IIPivot(?) Help
I agree with the others, but if you really wanted to do this in Power Query, the following code should get you started, but it will probably take a long time to execute with a large database.
let //Merge your dimension and fact tables Source = Table.NestedJoin(Table1, {"BOL"}, Table2, {"BOL"}, "Table2", JoinKind.Inner), //Expand the unique table columns #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"charge_description", "charge_retail", "charge_cost"}), //unpivot #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Table2", {"BOL"}, "Attribute", "Value"), //Group by BOL //Generate an Index depending on the number of items in each group // You are showing five (5) items per so we divide by 5 // Then combine the various costs and the divided index to create what will be our pivoted columns #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"BOL"}, { {"Pivot", (t)=> [a=Table.AddIndexColumn(t,"Index",0, 1, Int64.Type), b=Table.AddColumn(a,"Costs", each Number.IntegerDivide([Index],5)+1, Int64.Type), c=Table.RemoveColumns(b,"Index"), d=Table.TransformColumnTypes(c,{"Costs", type text}), e=Table.ReplaceValue( d, each [Attribute], each [Costs], (x,y,z) as text => if y <> "Retail Price" and y <>"Cost" then y & z else y, {"Attribute"}), f=Table.RemoveColumns(e,"Costs"), g=Table.Pivot(f,List.Distinct(f[Attribute]),"Attribute","Value", List.First)][g]}}), //List all column headers so we can make sure we have them all and then // do a custom sort so they will appear in the desired order #"All Col Hdrs" = List.Distinct(List.Combine(List.Transform(#"Grouped Rows"[Pivot], each List.Skip(Table.ColumnNames(_))))), #"Sorted Col Hdrs" = [a=List.Transform(#"All Col Hdrs", each Splitter.SplitTextByCharacterTransition((c)=>not List.Contains({"0".."9"},c),{"0".."9"})(_)), b=List.Sort(a, { {each Number.From(_{1}?), Order.Ascending}, {each List.PositionOf( {"charge_description","charge_retail","charge_cost"},_{0}),Order.Ascending}}), c=List.Transform(b, each Text.Combine(_))][c], //expand the pivoted column and set the data types #"Expand Pivot" = Table.ExpandTableColumn(#"Grouped Rows","Pivot",#"Sorted Col Hdrs"), #"Changed Type" = Table.TransformColumnTypes(#"Expand Pivot", List.Transform(#"Sorted Col Hdrs", each if Text.Contains(_,"description") then {_, type text} else {_, Currency.Type})) in #"Changed Type"From your data:
Super UserHi WorldWide1,
of course, I don't know what you plan to do with your data, but I have my doubts as to whether the approach described is the best one. Your first table is a wonderful dimension table including retail price and cost for every BOL (does that mean bill of lading?). Your second table looks like a fact table. Load both into Power BI and define a relation between the to tables using the two BOL columns. I would choose this approach for almost any scenario.
WorldWide1Thank you for the response. I get how using relationships is the way to go - yet I need everything on one line by BOL (yes, Bill of Lading) for my team for further use. It can't look like this:
and, I don't have the final list yet but we may have as many as 50 Charge Items I need to detail.
I'm probably still missing something or making this harder than it needs to be, but I need all of the data related to each BOL on a single row.
Thank you.
- Chewdata
Responsive Resident
Hey!
as lbendlin and Hans-Georg_Puls mentioned you should try to use relationships instead.
Not completly sure as what you want to achieve. Something else you can try is get Retail Price and Cost as a row with their own charge_description (something like BASE_COST) in the facttable (second table) changing the columnnames for Retail price and cost to charge_ratail and charge_cost.
Then you can use a matrix table with charge_description as column and charge_ratail and charge_cost as values
