Splitting Tables

[Babycakes_00]

Hi There

I have a several tables in one form that I need to split via power query

What steps do I need to take?

Thanks for any help?

[Table: 1]
SubmissionID	FormID	FormName	CreatedDate	SubmittedDate	UserID	UserName
[Table: 2]
SubmissionID	EntryID	Name	Value
[Table: 3]
SubmissionID	EntryID	Index	Group	RowType	Column	Value
[Table: 4]
SubmissionID	EntryID	Product	Field	Column	Value

[Omid_Motamedise]

Do you have several tables in several queries or all the tables are in one query?

[BA_Pete]

Hi @Babycakes_00 ,

You can create a column in this table which you can use to filter for referenced tables. So your original table, lets call it 'SourceTable' will have a column added like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wig5JTMpJtVIwjFXSUUJHsTrRSsGlSbmZxcWZ+XmeLkAxt/yiXDjDLzE3Fch0LkpNLElNcQESQB5YQwmCH1qcWgTWAWKAdYCMxWEbDmGYK42IdKVrXklRJZgFdWJYYk5pKlFWkecwY5Id5pmXkloBpN2L8ksLgHRQfnlIZQE4OPNzSnPz4I4mwnYTkm0PKMpPKU0uAcVjZmpOCqatIANiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
    repBlankNull = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}),
    
// Relevant steps from here ====>
    addTableName = Table.AddColumn(repBlankNull, "TableName", each try if Text.Contains([Column1], "Table:") then [Column1] else null otherwise null),
    fillDownTableName = Table.FillDown(addTableName,{"TableName"})
in
    fillDownTableName

Which looks like this:

You can then disable load on this table, but reference it for the split-out tables, something like this:

let
    Source = Table.SelectRows(SourceTable, each Text.Contains([TableName], "1"))
in
    Source

Unfortunately, it's very difficult to do this any more dynamically as Power Query can not spawn new queries within itself.

Pete