Pivot(?) Help

[WorldWide1]

Afternoon all --

Watched some videos and have been working this data around but can't quite get the data organized how I need it.  Any help appreciated.

I have a simple base table structured by rows:

I need to join/merge a vertical table that looks like this, w/the above base table:

So the result should look something like this, where all the data is on one row:

Would prefer to do the work in the Power Query, but doesn't necessarily need to be there.

Appreciate any thoughts or help on this.

Thank you.

[ronrsnfld · Accepted solution]

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:

[WorldWide1]

Thank you very much!

[lbendlin]

As @DNMAF mentioned this is the antithesis to what Power BI is about. Avoid merges and pivots in Power Query. Let the data model and the visuals do the work for you.

[DNMAF]

Hi @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.

[WorldWide1]

Thank 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]

Hey!

as @lbendlin and @DNMAF  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