Forum Discussion

Ryan Hilton's avatar
Ryan Hilton
Copper Contributor
Aug 31, 2018

Duplicate Data

I have a spreadsheet with 4,000 lines. When I am extracting the data from the original source, its in a 1 to 1 relationship. How do I get it from a 1 to 1 relationship to 1 to many relationship in ex...
excel
Ryan Hilton's avatar
Ryan Hilton
Copper Contributor
Sep 04, 2018

Great, this is exactly what I was looking for. Is it possible to do this is power BI before I load the formula into excel?

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 04, 2018

Ryan, do you mean to do that transformation with Power Query into separate table?

If you publish that workbook on Power BI services you may do what you want with it at any time.

  • Ryan Hilton's avatar
    Ryan Hilton
    Copper Contributor
    Sep 11, 2018

    I ran into some issues with the data. What changes would I need to make to the formula if items owned is not consistent among owners? I have corrected my sample data and uploaded it again.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 11, 2018

      Hi Ryan,

       

      You may do transformation in Power Query (aka Get & Transform) like this

      let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{
        {"Owner of Product", type text},
        {"Itmes Owned", type text},
        {"Purchase Date", type date}}),
    AddIndex = Table.AddIndexColumn(ChangeType, "Index", 0, 1),
    CleanRepeatedOwners = Table.AddColumn(AddIndex, "CleanOwner", each
        if [Index] = 0 then [Owner of Product]
        else
            if AddIndex{[Index]-1}[Owner of Product] = [Owner of Product] then null
            else [Owner of Product]),
    RemoveInitialOwnersColumn = Table.RemoveColumns(CleanRepeatedOwners,{"Owner of Product"}),
    RenameNewColumnAsOld = Table.RenameColumns(RemoveInitialOwnersColumn,{{"CleanOwner", "Owner of Product"}}),
    KeepColumns = Table.SelectColumns(RenameNewColumnAsOld,{"Owner of Product", "Itmes Owned", "Purchase Date" })
in
    KeepColumns

      and load result back into the Excel sheet.

      Attached.

Resources