Split Master table into multiple tables dynamically based on column value

[Richard_Halsall]

Hi all

I need to split a 'time recording' table into multiple tables (with exactly the same columns) based on the value in a single column named asset. The business use case is that the data needs to be viewed on individual tabs (I'm having to use Excel) for each asset.

I am aware I can group the data and manually click 'Add as New Query'

but I need to automate this functionality as the asset names will frequently change and I would not want the end user accessing power query

Sample data is below

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdJBC4IwFAfwrzI8l+XIyqOnoLQ6SB1ExGzmSDfZltC3zyIwQcOnh20weL/xf3u+r+15aEtJVGha1lKbaN/DE1FJMjRFhySRVJHqCutmtdtZxLRgAilcV2vLJSnSf4VnLu6U3apKjz/iFHk0r1/d0fxCREae/QT7mlNGpRKRomWtnCiLCVMf4+xtkLFGM4QcF+O51YuZN1vQxzgKUkRvgLNhQKMbKx2DgY4UzZYOjQJX2vLAlY5Q0B9uiwQ12gK1Go6LsGEtRk3aH6OZxRgNQMthcwYMMloZZECmDBhopPErBC8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [asset = _t, #"Project Name" = _t, #"Task Category" = _t, #"Task Duration" = _t, Name = _t])
in
Source

From which I would like to see 3 tables as there are 3 unique asset values

Any help would be much appreciated

[BA_Pete · Accepted solution]

Hi @Richard_Halsall ,

The simple answer is no, you can't dynamically create these tables. Power Query can not spawn new queries internally.

However, you could potentially pre-build the queries, but dynamically populate them something like the attached PBIX.

This works ok if you've got a limited number of assets and a known maximum but, if you've got maybe 20+, this is going to be a bit of a PITA.

Pete

[BA_Pete]

Also, genuine thank you for providing source data as M code. Makes it soooo much quicker/easier to grab and get started on 🙂

Pete