Power Query - Append Data

[Anonymous]

Hello all,

I currently have the following problem with a data set. The data is delivered in "data blocks" and not in a database notation. Now I am trying to transform them with the help of the query editor so that I can work with them easily.

Can anyone of you maybe help me? With which function do I get my data transformed as desired?

Thank you!

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

1. Load this file and use the headers as first row.
2. Transpose it
3. Format the column1 which contains "name","age" and "size".
4. Add index and pivot the column2 which contains values of name, age, and size.
5. Fill up Age and Size.
6. Remove index and filter out the name where is null.

Result:

Code you can refer:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtSS1S0lEyNAUR5hZAEogCEktzQHwLMGEAEfRKzcurBNJGxiBRM1Ol2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Age = _t, Size = _t, #"(blank)" = _t, Name.1 = _t, Age.1 = _t, Size.1 = _t, #"(blank).1" = _t, Name.2 = _t, Age.2 = _t, Size.2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Age", Int64.Type}, {"Size", Int64.Type}, {"(blank)", type text}, {"Name.1", type text}, {"Age.1", Int64.Type}, {"Size.1", Int64.Type}, {"(blank).1", type text}, {"Name.2", type text}, {"Age.2", Int64.Type}, {"Size.2", Int64.Type}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type text}, {"Column9", type text}, {"Column10", type any}, {"Column11", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1.2"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Column1.1]), "Column1.1", "Column2"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"(blank)"}),
    #"Filled Up" = Table.FillUp(#"Removed Columns1",{"Age", "Size"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Filled Up",{"Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns2", each [Name] <> null and [Name] <> "")
in
    #"Filtered Rows"

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Unpivot, and then pivot should do it

https://radacad.com/pivot-and-unpivot-with-power-bi

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.