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