Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

One Project with Potential Several Bidders Dataset

Hello to Power BI gurus here!

 

I'm not new to Power BI. But I still encounter obstacles on the way of conquering Power BI from time to time. I have a hate and love relationship with Power BI.

 

So right now I got stuck in a report I’m putting together.

 

In the data sheet, there are bidding jobs. With each bidding job, there are one or more than one awarded bidder. We download the job list from a service website. But they don’t know who are the awarded bidders or how many in advance. Therefore I don’t know how many cells I need to save for each job.

 

The table below is just a much simplified sample sheet. Also all bidders for a job are in the same cell. It’s just for you to know there are more than one bidder for some jobs. However, we can’t record data like this since Power BI won’t read different bidders if they are in the same cell. We know the jobs first. So the sheet will have Job Name column filled out. Our sales will fill out the two bidders columns once they find out the information later.

 

I need to create data analysis charts using this data table. How can I solve this problem of unknow number bidders so I can continue data analysis?  

 

District

Job Name

Part 1 Awarded Bidder(s)

Part 2 Awarded Bidder(s)

A

Job A

Bidder A, Bidder B

Bidder 1

B

Job B

Bidder C

Bidder 2, Bidder 3

C

Job C

Bidder D, Bidder E, Bidder F

Bidder 4, Bidder 5, Bidder 6

 

Big fat thanks in advance!

Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Here is one way to do it in the query editor that should set you up for analysis.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfLKT1IA0U6ZKSmpRchMQ6VYnWglJ6gaJ4SEk44ClOWMEDSCCxqD9TlD9YGVeIIlXOBKXOEsN4QJJnBBUzjLTCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [District = _t, #"Job Name" = _t, #"Part 1 Bidders" = _t, #"Part 2 Bidders" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"District", type text}, {"Job Name", type text}, {"Part 1 Bidders", type text}, {"Part 2 Bidders", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"District", "Job Name"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.Split([Value], ",")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Bidder"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Bidder", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Bidder", Text.Trim, type text}})
in
    #"Trimmed Text"

     

    Regards,

    Pat

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Here is one way to do it in the query editor that should set you up for analysis.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfLKT1IA0U6ZKSmpRchMQ6VYnWglJ6gaJ4SEk44ClOWMEDSCCxqD9TlD9YGVeIIlXOBKXOEsN4QJJnBBUzjLTCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [District = _t, #"Job Name" = _t, #"Part 1 Bidders" = _t, #"Part 2 Bidders" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"District", type text}, {"Job Name", type text}, {"Part 1 Bidders", type text}, {"Part 2 Bidders", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"District", "Job Name"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.Split([Value], ",")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Bidder"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Bidder", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Bidder", Text.Trim, type text}})
in
    #"Trimmed Text"

     

    Regards,

    Pat