How do I calculate proportional values depending on multiple variables? - Rainfall data

OllieSvT,

I would start by transforming the Station Apportionment table so it's in a usable format. Copy the steps beginning with UnpivotColumns into your query.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "Xc+9CsIwFAXgV5FMCqU0/2ZUR8GlgkPpENs7SK9tqRbx7U1uqxSXe0JIvpwUBduNNcKbJYxLbrOQRwCEkDmPQ4XhXOYkJbcupJQyHkyNFpRS0XaqtTGsTAq2h7r1iDManRO8+m54rta5x67fRFmEMaFaLx4hSNEyVTozcYtM9FWDvoFZFf9VF8ZckDoKYamjpRtEHfy9v3Xt6gzD4KsJzEz86QUQ4XHtxqElV/46bt3k2y+sjebUUaotweUH",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        Source_Name = _t,
        SAP_SITE_ID = _t,
        First_Station_Name = _t,
        Second_Station_Name = _t,
        Third_Station_Name = _t,
        First_Station_Number = _t,
        Second_Station_Number = _t,
        Third_Station_Number = _t,
        First_State_Proportion = _t,
        Second_State_Proportion = _t,
        Third_State_Proportion = _t
      ]
  ),
  ChangeType = Table.TransformColumnTypes(
    Source,
    {
      {"Source_Name", type text},
      {"SAP_SITE_ID", Int64.Type},
      {"First_Station_Number", Int64.Type},
      {"Second_Station_Number", Int64.Type},
      {"Third_Station_Number", Int64.Type},
      {"First_State_Proportion", type number},
      {"Second_State_Proportion", type number},
      {"Third_State_Proportion", type number}
    }
  ),
  UnpivotColumns = Table.UnpivotOtherColumns(
    ChangeType,
    {"Source_Name", "SAP_SITE_ID"},
    "Attribute",
    "Value"
  ),
  SplitColumn = Table.SplitColumn(
    UnpivotColumns,
    "Attribute",
    Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false),
    {"Attribute.1", "Attribute.2"}
  ),
  PivotColumns = Table.Pivot(
    SplitColumn,
    List.Distinct(SplitColumn[Attribute.2]),
    "Attribute.2",
    "Value"
  ),
  RemoveColumn = Table.RemoveColumns(PivotColumns, {"Attribute.1"}),
  FilterNull = Table.SelectRows(RemoveColumn, each ([Station_Number] <> null)),
  ChangeType2 = Table.TransformColumnTypes(
    FilterNull,
    {{"Station_Name", type text}, {"Station_Number", type text}, {"State_Proportion", type number}}
  )
in
  ChangeType2

You'll need to provide sample data for the Daily Rainfall table (not a screenshot).