Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
bhaskarpbi999
Helper III
Helper III

Group data and create columns for weekdays and Time values

Hi All,

I need your assitance to shaep the data to the expected format.

 

Current Data Format

 

bhaskarpbi999_0-1709561115826.png

Expected result the Week days needs to be columns and Time should be like below.

 

bhaskarpbi999_1-1709561242084.png

 

 

Regards,

Bhaskar

 

1 ACCEPTED SOLUTION

dufoq3_0-1709722533558.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZXJCsIwEIZfZeg5atKKbb2JCxZcilY8VA8BFyJpKq2+v3VARIxHyVwmmZDl48vhz3NPiCCMPeYNy+qmLkrrsob1VSrTrI2mM0gHs/lyMV4z2FU7M+zAqJJnWSgjawYiYpCWug2JObQhlboozbGGJMHNr6PY4Ds483kARXO7uWv9HoS3Zy5p5sum8LDPeYtHTSWDFD+RRJcQkughEhFL2YacJUSiZWk7JmcJkWhZyqbkLCESLUuTFTlLiPTDUvTsMlmpk/w3TRD7oSXbfJcolhRxz/P5VU55LPnhnoeQH0tyuOch5MeSGe55CPmxpIV7ni8/+wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Postal = _t, #"City Country" = _t, #"Collect Point" = _t, Date = _t, Time = _t, Index = _t]),
    FilteredRows = Table.SelectRows(Source, each ([Date] <> "null" and [Date] <> null)),
    GroupedRows = Table.Group(FilteredRows, {"Collect Point", "Date"}, {{"All", each Table.AddIndexColumn(_, "ID", 1, 1, type text), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    AddedPrefix = Table.TransformColumns(CombinedAll, {{"ID", each "Time" & Text.From(_), type text}})
in
    AddedPrefix

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

34 REPLIES 34
bhaskarpbi999
Helper III
Helper III

Hi Dufo,

 

Many Thanks for your support and solution. Kudos to you for prompt response thanks a lot.

 

Regards,

Bhaskar

You should accept my answers as solutions. You're welcome.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

dufoq3
Super User
Super User

@bhaskarpbi999

 

Result:

dufoq3_0-1709580672678.png

 

 

v1 (will work only if you have 2 open hours for each day)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZXLCsIwEEV/Zeg6atOKtu6kKhZaLVpxoV0EfBBJU2n1/60DImJcSmYzyYQ8DieLu9s5nPvD0GFOVNU3eZFKVQ2sr0Lqdm0yTyAbJ+lyMV0z2Nd7HfVgUouzKKUWDQMeMMgq1YVYH7qQCVVW+thAHOPm11Fs8B2cea4PZXu7viv1HrhTMJs06bItbjhy3Q7vt5UKEh8gUkADKd+Qs4RItCxtp+QsIRItS/mcnCVEomVptiJnCZF+WAqeXS5qeRL/pvFDb2gIEs8miiFF7PN8fpVVHkN+2Och5MeQHPZ5CPkxZIZ9HkJ+DGlhn+fLT/EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Postal = _t, #"City Country" = _t, #"Collect Point" = _t, Date = _t, Time = _t, Index = _t]),
    TransformCollectPoint = Table.TransformColumns(Source, {{"Collect Point", each Text.Combine(List.Transform(Text.Split(_, "#(lf)"), (x)=> Text.Trim(x, {",", " ", "#(cr)", "#(lf)"})), " | "), type text}} ),
    FilteredRows = Table.SelectRows(TransformCollectPoint, each ([Date] <> "null" and [Date] <> null)),
    Days = #table(type table[Short=text, Long=text], {{"MO", "Monday"}, {"TU", "Tuesday"}, {"WE", "Wednesday"}, {"TH", "Thursday"}, {"FR", "Friday"}, {"SA", "Saturday"}, {"SU", "Sunday"}}),
    DaysFiltered = Table.Buffer(Table.SelectRows(Days, each List.Contains(List.Distinct(FilteredRows[Date]), [Short]))),
    StepBack = FilteredRows,
    PivotedColumn = Table.Pivot(StepBack, List.Distinct(StepBack[Date]), "Date", "Time", each _),
    RenamedColumns = Table.RenameColumns(PivotedColumn, Table.ToRows(DaysFiltered), MissingField.Ignore),
    TransformDayColumns = Table.TransformColumns(RenamedColumns, List.Transform(DaysFiltered[Long], (colName)=>
     { colName, each Text.Combine(_, "||"), type text } )),
    SplitColumnByDelimiter = List.Accumulate( 
     List.Zip( { List.Repeat({"1"}, List.Count(DaysFiltered[Long])), List.Repeat({"2"}, List.Count(DaysFiltered[Long])), DaysFiltered[Long] } ),
     TransformDayColumns,
     (s,c)=> Table.SplitColumn(s, c{2}, Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), { c{2} & " Open / Close " & Text.From(c{0}), c{2} & " Open / Close " & Text.From(c{1}) })
)
in
    SplitColumnByDelimiter

 

 

v2 (will work for any number of open hours for each day)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZXJCsIwEIZfZeg5atKKbb2JCxZcilY8VA8BFyJpKq2+v3VARIxHyVwmmZDl48vhz3NPiCCMPeYNy+qmLkrrsob1VSrTrI2mM0gHs/lyMV4z2FU7M+zAqJJnWSgjawYiYpCWug2JObQhlboozbGGJMHNr6PY4Ds483kARXO7uWv9HoS3Zy5p5sum8LDPeYtHTSWDFD+RRJcQkughEhFL2YacJUSiZWk7JmcJkWhZyqbkLCESLUuTFTlLiPTDUvTsMlmpk/w3TRD7oSXbfJcolhRxz/P5VU55LPnhnoeQH0tyuOch5MeSGe55CPmxpIV7ni8/+wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Postal = _t, #"City Country" = _t, #"Collect Point" = _t, Date = _t, Time = _t, Index = _t]),
    TransformCollectPoint = Table.TransformColumns(Source, {{"Collect Point", each Text.Combine(List.Transform(Text.Split(_, "#(lf)"), (x)=> Text.Trim(x, {",", " ", "#(cr)", "#(lf)"})), " | "), type text}} ),
    FilteredRows = Table.SelectRows(TransformCollectPoint, each ([Date] <> "null" and [Date] <> null)),
    UppercasedText = Table.TransformColumns(FilteredRows,{{"Date", Text.Upper, type text}}),
    Days = #table(type table[Short=text, Long=text], {{"MO", "Monday"}, {"TU", "Tuesday"}, {"WE", "Wednesday"}, {"TH", "Thursday"}, {"FR", "Friday"}, {"SA", "Saturday"}, {"SU", "Sunday"}}),
    DaysFiltered = Table.Buffer(Table.SelectRows(Days, each List.Contains(List.Distinct(UppercasedText[Date]), [Short]))),
    StepBack = UppercasedText,
    PivotedColumn = Table.Pivot(StepBack, List.Distinct(StepBack[Date]), "Date", "Time", each _),
    RenamedColumns = Table.RenameColumns(PivotedColumn, Table.ToRows(DaysFiltered), MissingField.Ignore),
    // Moze sa stat, ze pre niektory den ma napriklad troje rozne otvaracie hodiny.
    MaxOpenHoursCount = List.Max(
    List.TransformMany( Table.ToColumns(Table.SelectColumns(RenamedColumns, DaysFiltered[Long])),
       each {List.Transform(_, List.Count)},
       (x,y)=> List.Max(y)
)),
    StepBack2 = RenamedColumns,
    TransformDayColumns = Table.TransformColumns(StepBack2, List.Transform(DaysFiltered[Long], (colName)=>
     { colName, each Text.Combine(_, "||"), type text } )),
    SplitColumnByDelimiter = List.Accumulate( 
     DaysFiltered[Long],
     TransformDayColumns,
     (s,c)=> Table.SplitColumn(s, c, Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), List.Transform({1..MaxOpenHoursCount}, (x)=> c & " Open / Close " & Text.From(x) ))
),
    RemovedEmplyColumns = Table.RemoveColumns(SplitColumnByDelimiter, List.Transform(List.PositionOf(List.Transform(Table.ToColumns(SplitColumnByDelimiter), each if List.Count(List.RemoveNulls(_)) = 0 then true else false), true, Occurrence.All), each Table.ColumnNames(SplitColumnByDelimiter){_}))
in
    RemovedEmplyColumns

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

bhaskarpbi999
Helper III
Helper III

I have shared the sample data with google drive link

And now allow acces for everyone please 😉


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Provided link access to every one.

bhaskarpbi999
Helper III
Helper III

Hi dufo,

 

Thanks for your prompt response and i have pasted the data table.

 

Thanks for your support.

 

Regards.

Bhaslar

It is a screenshot.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

PostalCity CountryCollect PointDateTimeIndex
11379 Cortijillos Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

203 m
  1
11379 Cortijillos Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

203 m
MO 09:00-14:001
11379 Cortijillos Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

203 m
MO 16:00-18:001
11379 Cortijillos Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

203 m
TU 09:00-14:001
11379 Cortijillos Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

203 m
TU 16:00-18:001
11379 Cortijillos Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

203 m
WE 09:00-14:001
11379 Cortijillos Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

203 m
WE 16:00-18:001
11379 Cortijillos Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

203 m
TH 09:00-14:001
11379 Cortijillos Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

203 m
TH 16:00-18:001
11379 Cortijillos Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

203 m
FR 09:00-14:001
11379 Cortijillos Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

203 m
FR16:00-18:001
11380 Tarifa Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

23927 m
  2
11380 Tarifa Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

23927 m
MO 09:00-14:002
11380 Tarifa Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

23927 m
MO 16:00-18:002
11380 Tarifa Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

23927 m
TU 09:00-14:002
11380 Tarifa Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

23927 m
TU 16:00-18:002
11380 Tarifa Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

23927 m
WE 09:00-14:002
11380 Tarifa Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

23927 m
WE 16:00-18:002
11380 Tarifa Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

23927 m
TH 09:00-14:002
11380 Tarifa Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

23927 m
TH 16:00-18:002
11380 Tarifa Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

23927 m
FR 09:00-14:002
11380 Tarifa Spain DHL PALMONES,

C/ Dragaminas, 18, Pol. Ind. Palmones II

PALMONES

11379

23927 m
FR16:00-18:002
dufoq3
Super User
Super User

Hi,

  1. you sample data doesn't match with result...
  2. provide sample data as table or upload it and paste here a link so we can copy it

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

I have shared the sample data with google drive link . thanks for your support.

Hi Dufo,

 

I need above required format with ID column. thanks for your support

Hi Dufo,

Thanks for your support.

I need your assitance to get ID with time1 and time2 for each weekday which is final solution,

 

 

 

bhaskarpbi999_0-1709647787514.png

 

Could you provide expected result?


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Hi Dufo,

I need your assitance to get ID with time1 and time2 for each weekday which is final solution,

 

bhaskarpbi999_0-1709651140068.png

 

I've asked for expected result...


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Hi Dufo,

I need your assitance to create custom colum ID with time1 and time2 for each weekday which is final solution highlighted in yellow below scrrenshot

 

bhaskarpbi999_0-1709659530325.png

Regards,

Bhaskar

You asked for this result (which I've created already):

dufoq3_0-1709664791468.png

Now you are sending a screenshot of SOURCE (not result) and to be honest I'm not sure what do you need. So please, send me a screenshot of FINAL EXPECTED RESULT.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.