Transpose table to get the data in the required format in Power Query.

[Anonymous]

Hi Community,

I am getting data from Azure SQL database into Power BI Desktop and trying to shape the data in the required format.

1st screenshot below is how the data is looking like in Power Query at the moment:

As you can see the TaskExecutionID, LoadDateTime and PackageNumber are repeated. I need a Unique PackageNumber followed by all the 22 values under FullyQualifiedName column should be columns with all the corresponding values from the current Values column.

I am looking to achieve the 2nd screenshot below. It would be nice to have the TaskExecutionID and LoadDateTime prefixed to each line.

After that, I will clean the data by removing special characters in values such as [, ], ", _singleSelect, etc.

I have tried various options using Transpose, Pivot, and Unpivot and am sure I'm missing that 1 little step in getting to what I need.

Can anyone please help me with how to achieve this if you had been in a similar situation.

Any info with screenshots or guidelines would be of great help to me and others similar.

Hoping to hear soon.

Thanks for your time in advance.

Regards, B

[danextian]

Hello @Anonymous ,

Please post a sample of your data -- one that we can easily copy-paste.
Also, you may look at this pretty similar post.

[Anonymous]

@danextianThanks for your quick reply.

Here is the link to the Spready with As-Is Data (the data structure I got into Power Query) and To-Be Data (the format in which I want the data).

https://docs.google.com/spreadsheets/d/1u9-nSM277TwANIMAXrkZnNrzSIsJ7CN9SCNjxql6tTs/edit#gid=1327783...

Hope this helps.

Which similar post were you referring to, can you please share the link(s)?

Thanks,

Chandu  

[Anonymous]

Anyone.. please..?

Thanks

[danextian]

Hi @Anonymous ,

I remember creating a custom function before to extract data from a text string so I tried it on yours (here's the original post ustom Function to Create a Table from a Text String) . Please follow the steps below.

• Create a custom function using Blank Query from Get Data and name it fnTableFromString. Paste the code below:
  

let func =     
    (MyString as text, Del as text, ToText as any) as table =>


let

    //splits text by delimiter
    TextSplit = Text.Split(MyString, Del),
    
    //returns values from text string assuming the values start from position 1 with interval of  2
    ValuesList = List.Alternate(TextSplit,1,1),

    //returns field names from text string, those not found in ListValues are field names
    FieldsList = List.RemoveMatchingItems(TextSplit, ValuesList),
    
    //creates a table from ListValues and ListFields
    TableFromLists = Table.FromColumns({FieldsList, ValuesList}),

    //transposes the created table
    TransposedTable = Table.Transpose(TableFromLists),

    //promotes first row to headers
    PromotedHeaders = Table.PromoteHeaders(TransposedTable, [PromoteAllScalars = true]),

    //converts column types to text
    DataTypesToText = Table.TransformColumnTypes( PromotedHeaders, List.Transform(Table.ColumnNames(PromotedHeaders), each {_, type text}) ),

    
    //converts to text?
    Converted = if ToText = null then PromotedHeaders else DataTypesToText 
    
  
in
   DataTypesToText, 


    //meta-record for documentation purposes
    documentation = [
    Documentation.Name =  "Table From String", 
    Documentation.Description = "Splits <code>MyString </code> into columns per each occurence of <code>Del</code>. The generated columns are converrted to text if
                                    <code>ToText</code> is not null (this applies to primitive values only as the columns from the table generated when this function is invoked
                                     are by default of data type any.",
    Documentation.LongDescription = "Splits <code>MyString </code> into columns per each occurence of <code>Del</code>. The generated columns are converrted to text if
                                    <code>ToText</code> is not null (this applies to primitive values only as the columns from the table generated when this function is invoked
                                     are by default of data type any.",
    Documentation.Source = "https://community.powerbi.com/t5/user/viewprofilepage/user-id/15697",
    Documentation.Author = "Dane Belarmino",
    Documentation.Examples = {
            [Description =  "To reach out to the author, please go to https://community.powerbi.com/t5/user/viewprofilepage/user-id/15697.", 
            Code  = "(""PartyName$#$Dane$#$SoldTo$#$123456$#$Country$#$United States$#$State$#$New York"", ""$#$"", null)",
            Result= "#table({""PartyName"",""SoldTo"",""Country"",""State""},{{""Dane"",""123456"",""United States"",""New York""}})" 
            // 
            ]
            }
            ]       
     
in 
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))​

Modify the code below to point to your data source. You may point this to the local copy of the shared Excel file in your G Drive  so you can see what each step does. 

let
    Source = Excel.Workbook(File.Contents("C:\Users\username\Downloads\Sample Data Sheet.xlsx"), null, true),
    #"As Is Data_Sheet" = Source{[Item="As Is Data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"As Is Data_Sheet", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each [TaskExecutionID] <> null and [TaskExecutionID] <> ""),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"TaskExecutionID", Int64.Type}, {"LoadDateTime", type datetime}, {"PackageNumber", type text}, {"FullyQualifiedName", type text}, {"Values", type text}}),
    #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Changed Type", {{"Values", each Text.BetweenDelimiters(_, "[", "]"), type text}}),
    #"Extracted Text Between Delimiters1" = Table.TransformColumns(#"Extracted Text Between Delimiters", {{"Values", each Text.BetweenDelimiters(_, """", """"), type text}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Extracted Text Between Delimiters1", "Merged", each Text.Combine({[FullyQualifiedName], [Values]}, "$#$"), type text),
    #"Replaced Value" = Table.ReplaceValue(#"Inserted Merged Column","",null,Replacer.ReplaceValue,{"Values"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"TaskExecutionID", "LoadDateTime", "PackageNumber"}, {{"String", each Text.Combine([Merged], "$#$"), type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fnTableFromString", each fnTableFromString([String], "$#$", null))
in
    #"Invoked Custom Function"

[Anonymous]

Hi@danextian

Thank you so much for your time and quick response.

Just to let you know again that, I'm getting data from Azure SQL Database (import).

So, should I do the fnTableFromString after I import data from Azure into PBI or do it before and then Get Data from Azure?

Where would I have to do the second bit of coding in this scenario?

Sorry for my silly questions, but I haven't done this before.

Thanks, heaps in advance.

Regards,

Chandu

[danextian]

Hi @Anonymous ,

I have never connected to Azure so I am not sure what steps Power BI creates after connecting but the customization in the code I posted is after Promoted Headers step. In the Advanced Editor, you  might need to replace this part of the code with what Power BI automatically generates.

let
    Source = Excel.Workbook(File.Contents("C:\Users\dcbelarmino\Downloads\Sample Data Sheet.xlsx"), null, true),
    #"As Is Data_Sheet" = Source{[Item="As Is Data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"As Is Data_Sheet", [PromoteAllScalars=true]),

[Anonymous · Accepted solution]

Hi @danextian 

I really appreciate your time and prompt response. Before trying your code, I've tried another way and got everything in place (i.e. all rows values from column 'FullyQualifiedName' went into new columns and all the rows from column 'Values' went under each new column respectively) which is exactly what I was looking for as per the 'To Be' spreadsheet in the links I've shared earlier.

All I did was -> Select the 2 columns I am interested -> Pivot Column -> Values -> Don't Aggregate (Very Important); and everything fell in place automatically!

Attached are the screenshots if anyone with similar requirements. Hope this helps.

Thanks & regards,

Chandu

[danextian]

It is great that you were able to figure it out. 

[danextian]

Ooops. I forgot to post the link. Here it is Re: Rows to column: is unpivot realy the solution?

By the way, I  don't see any attached file or a link to it.

[Anonymous]

@danextian 

After realizing there is no way to upload my excel file on in here, I was actually trying to upload and share the google drive link to you and got your message landed up already.

Thanks for the link, I will read through it.

It would be great if you can please share your ideas to get my issue resolved after seeing the Spready with data.

Many thanks,

Chandu