Rows to column: is unpivot realy the solution?

[Sibrulotte]

Hi, 

I have a crappy text report... 😉

and in that text report I have something like this 

Account        Registration    Date of activity 

624564         John Doe          2019-11-30

                     30 Slipery rd

                      Denver, Co

                     10012, US

623134        Jane Smith          2019-11-30

                    58 Poplar st

                    apt 202

                    Crownville, Ut

                     15420, US

And so forth...

Did you see the catch there too? John has 3 adress lines, Jane has 4.

Can you see me coming? 

How do I get these adresses all ligned up in seperate columns for street + apt number, city, state, zip and country?

[v-lili6-msft]

hi @Sibrulotte 

Usually, convert row to column, you need use pivot or unpivot column.

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

but for your case, it seems that you could use split column in power bi

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

If not your case, please share the sample data in table format and your expected output.

Regards,

Lin

[Sibrulotte]

Account	Resgistration	Date of activity
624564	John Doe	2019-11-29
	30 Slipery rd
	Denver, Co
	10012, US
623134	Jane Smith	2019-11-30
	58 Poplar st
	apt 202
	Crownville, Ut
	15420, US

Result would be:

Account	Name	Adress	City	State	Zip	Country
624564	John Doe	30 Slipery rd	Denver	Co	10012	US
623134	Jane Smith	58 Poplar st, apt 202	Crownville	Ut	15420	US

[v-lili6-msft]

hi @Sibrulotte 

For your case, you could try this way:

Step1:

You need add a column tothat is used for classify the [Resgistration] column

for example:

Step2:

Then use Pivot column and split column as these steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY49C4MwFEX/SsgcMB8qdSw6dSiF4FJxCBgwEJMQXy3++walnVJ4w72cy+MMA655WdUlJvjmZ4c6r1NkrOBNwSlrUrmrReORDDhlQZG0Jui4ozilnu46Rb2u30Gn3aYjaf0JWwM7kaDg94FRyjjp5cmfJqTty0Hcj0HNBROHjHIaycXAfOoImtOpLujhg1URrZCzUQEQpzyH2ujfbjPWatLDX9mq5DQvO34A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Account = _t, Resgistration = _t, #"Date of activity" = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", Int64.Type}, {"Resgistration", type text}, {"Date of activity", type date}, {"Type", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Account", "Date of activity"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Account","Date of activity","Type"}, {{"Persons", each Text.Combine([Resgistration], ","), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Type]), "Type", "Persons"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Date of activity"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "City,State", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"City,State.1", "City,State.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"City,State.1", type text}, {"City,State.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"City,State.1", "City"}, {"City,State.2", "State"}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns", "Zip,Country", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Zip,Country.1", "Zip,Country.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Zip,Country.1", Int64.Type}, {"Zip,Country.2", type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Zip,Country.1", "Zip"}, {"Zip,Country.2", "Country"}})
in
    #"Renamed Columns1"

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[Sibrulotte]

well it was nice to try. My problem is exactly that: these rows do not have labels on them. And to make it worst, some have extra rows for appatment number, so I can't start an index and make a mathematical equation to derive a fix number for these rows...

[danextian · Accepted solution]

Hi @Sibrulotte ,

You can try grouping the rows in [Registration] column by [Account] and [Date] using Table.Group function. The aggregation parameter is modified to combine the rows instead of counting or summing them up. Once combined, you then need create custom columns by extracting the data from the grouped rows. Here's a sample code:

let
  Source = Excel.Workbook("file.xlsx", null, true),
  Navigation = Source{[Item = "table", Kind = "Sheet"]}[Data],
  #"Promoted headers" = Table.PromoteHeaders(Navigation, [PromoteAllScalars = true]),
  #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"Account", type text}, {"Registration", type text}, {"Date of activity", type date}}),
  #"Filled down" = Table.FillDown(#"Changed column type", {"Account", "Date of activity"}),
  #"Grouped rows" = Table.Group(#"Filled down", {"Account", "Date of activity"}, {{"Registration", each Text.Combine([Registration], "__"), type text}}),
  #"Inserted text after delimiter" = Table.AddColumn(#"Grouped rows", "Zip and Country", each Text.AfterDelimiter([Registration], "__", {0, RelativePosition.FromEnd}), type text),
  #"Inserted text between delimiters" = Table.AddColumn(#"Inserted text after delimiter", "City and State", each Text.BetweenDelimiters([Registration], "__", "__", {0, RelativePosition.FromEnd}, {0, RelativePosition.FromEnd}), type text),
  #"Inserted text before delimiter" = Table.AddColumn(#"Inserted text between delimiters", "Name", each Text.BeforeDelimiter([Registration], "__", 0), type text),
  #"Inserted conditional column" = Table.AddColumn(#"Inserted text before delimiter", "Street Address", each  Text.BetweenDelimiters([Registration], [Name] & "__", "__" & [City and State]), type text),
  #"Replaced value" = Table.ReplaceValue(#"Inserted conditional column", "__", ", ", Replacer.ReplaceText, {"Street Address"})
in
  #"Replaced value"

[Sibrulotte]

That's a very functional solution, and helps with 90% of the problem.

But I still don't have a clean delimiter for the rows that are appartment numbers and not the city. 

Remember, some rows are created for appartement numbers, and when there are no appartment numbers, that row is not created.

[PaulDBrown]

@Sibrulotte 

I know very little about M, but it sounds like the very first step that needs to be done is to ensure that each value in the first column has “n”  number of rows beneath (to cover the total rows possible per address). So adding one or two rows to reach “n”. And carry on from there...I guess...but I haven’t a clue if this is possible.

[danextian]

@PaulDBrown 

The M code I posted extracts the necessary info following this logic. 

• Row 1 = Name
• Last Row = Zip & Country
• 2nd to the last row = City & State
• Rows in between = Street, etc.

[Sibrulotte]

Oh that's a good move. 

Trying to recreate it in french. 

Sorry you're M code couldn't be copy pasted in my french version...

[v-lili6-msft]

hi @Sibrulotte 

You must define the labels for Registration columns, otherwise, it will be difficult to determine which rows is Name/Adress/City ,etc.

Then the following steps are simple.

So i think you'd better do it in the datasource.

Regards,

Lin